clear all
set more off
set mem 10000000
set matsize 10000
version 13

****************************************************************** 
*** Build File to Process Raw Census 2001 HPCA *******************
****************************************************************** 

** Set file paths
do "$path_code/paths.do"

********************************************************************************
********************************************************************************

** 2001 HPCA (block-level only)


** Extract variables from excel files, by state
{
{
clear
cd "$hpca01/H1"
local files: dir "$hpca01/H1/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G total_census_houses
  rename H vacant_census_houses
  rename I occupied_census_houses
  rename J occupied_ch_res
  rename K occupied_ch_otherres
  rename L occupied_ch_shopoffice
  rename M occupied_ch_school
  rename N occupied_ch_hotel
  rename O occupied_ch_hosp
  rename P occupied_ch_factory
  rename Q occupied_ch_relig
  rename R occupied_ch_nonres
  destring total_census_houses - occupied_ch_nonres, replace
  local state = st_code
  save "$hpca01/H1/`state'_H1.dta", replace
}
local statafiles: dir "$hpca01/H1/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
save "$hpca01/hpca01_stata/H1_all.dta", replace
}



{
cd "$hpca01/H2"
local files: dir "$hpca01/H2/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=5
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total
  rename H ch_good
  rename I ch_liveable
  rename J ch_dilapidated
  rename K res_total
  rename L res_good
  rename M res_liveable
  rename N res_dilapidated
  rename O other_total
  rename P other_good
  rename Q other_liveable
  rename R other_dilapidated
  destring ch_total - other_dilapidated, replace
  local state = st_code
  save "$hpca01/H2/`state'_H2.dta", replace
}
local statafiles: dir "$hpca01/H2/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_COUNT_H2 = ch_total
gen HPCA_t_hh_g = ch_good/ch_total
gen HPCA_t_hh_l = ch_liveable/ch_total
gen HPCA_t_hh_d = ch_dilapidated/ch_total
gen HPCA_t_hh_r = res_total/ch_total
gen HPCA_t_hh_r_g = res_good/res_total
gen HPCA_t_hh_r_l = res_liveable/res_total
gen HPCA_t_hh_r_d = res_dilapidated/res_total
gen HPCA_t_hh_r_o = other_total/ch_total
gen HPCA_t_hh_rc_g = other_good/other_total
gen HPCA_t_hh_rc_l = other_liveable/other_total
gen HPCA_t_hh_rc_d = other_dilapidated/other_total

save "$hpca01/hpca01_stata/H2_all.dta", replace
}
{
cd "$hpca01/H2SCA"
local files: dir "$hpca01/H2SCA/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=5
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total_sc
  rename H ch_good_sc
  rename I ch_liveable_sc
  rename J ch_dilapidated_sc
  rename K res_total_sc
  rename L res_good_sc
  rename M res_liveable_sc
  rename N res_dilapidated_sc
  rename O other_total_sc
  rename P other_good_sc
  rename Q other_liveable_sc
  rename R other_dilapidated_sc
  destring ch_total - other_dilapidated, replace
  local state = st_code
  save "$hpca01/H2SCA/`state'_H2SCA.dta", replace
}
local statafiles: dir "$hpca01/H2SCA/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_A_SC_COUNT_H2 = ch_total
gen HPCA_A_SC_t_hh_g = ch_good/ch_total
gen HPCA_A_SC_t_hh_l = ch_liveable/ch_total
gen HPCA_A_SC_t_hh_d = ch_dilapidated/ch_total
gen HPCA_A_SC_t_hh_r = res_total/ch_total
gen HPCA_A_SC_t_hh_r_g = res_good/res_total
gen HPCA_A_SC_t_hh_r_l = res_liveable/res_total
gen HPCA_A_SC_t_hh_r_d = res_dilapidated/res_total
gen HPCA_A_SC_t_hh_r_o = other_total/ch_total
gen HPCA_A_SC_t_hh_rc_g = other_good/other_total
gen HPCA_A_SC_t_hh_rc_l = other_liveable/other_total
gen HPCA_A_SC_t_hh_rc_d = other_dilapidated/other_total

save "$hpca01/hpca01_stata/H2SCA_all.dta", replace
}
{
cd "$hpca01/H2STA"
local files: dir "$hpca01/H2STA/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=5
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total_st
  rename H ch_good_st
  rename I ch_liveable_st
  rename J ch_dilapidated_st
  rename K res_total_st
  rename L res_good_st
  rename M res_liveable_st
  rename N res_dilapidated_st
  rename O other_total_st
  rename P other_good_st
  rename Q other_liveable_st
  rename R other_dilapidated_st
  destring ch_total - other_dilapidated, replace
  local state = st_code
  save "$hpca01/H2STA/`state'_H2STA.dta", replace
}
local statafiles: dir "$hpca01/H2STA/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_A_ST_COUNT_H2 = ch_total
gen HPCA_A_ST_t_hh_g = ch_good/ch_total
gen HPCA_A_ST_t_hh_l = ch_liveable/ch_total
gen HPCA_A_ST_t_hh_d = ch_dilapidated/ch_total
gen HPCA_A_ST_t_hh_r = res_total/ch_total
gen HPCA_A_ST_t_hh_r_g = res_good/res_total
gen HPCA_A_ST_t_hh_r_l = res_liveable/res_total
gen HPCA_A_ST_t_hh_r_d = res_dilapidated/res_total
gen HPCA_A_ST_t_hh_r_o = other_total/ch_total
gen HPCA_A_ST_t_hh_rc_g = other_good/other_total
gen HPCA_A_ST_t_hh_rc_l = other_liveable/other_total
gen HPCA_A_ST_t_hh_rc_d = other_dilapidated/other_total

save "$hpca01/hpca01_stata/H2STA_all.dta", replace
}
{
cd "$hpca01/H2A"
local files: dir "$hpca01/H2A/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=5
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total_a
  rename H ch_good_a
  rename I ch_liveable_a
  rename J ch_dilapidated_a
  rename K res_total_a
  rename L res_good_a
  rename M res_liveable_a
  rename N res_dilapidated_a
  rename O other_total_a
  rename P other_good_a
  rename Q other_liveable_a
  rename R other_dilapidated_a
  destring ch_total - other_dilapidated, replace
  local state = st_code
  save "$hpca01/H2A/`state'_H2A.dta", replace
}
local statafiles: dir "$hpca01/H2A/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_A_COUNT_H2 = ch_total
gen HPCA_A_t_hh_g = ch_good/ch_total
gen HPCA_A_t_hh_l = ch_liveable/ch_total
gen HPCA_A_t_hh_d = ch_dilapidated/ch_total
gen HPCA_A_t_hh_r = res_total/ch_total
gen HPCA_A_t_hh_r_g = res_good/res_total
gen HPCA_A_t_hh_r_l = res_liveable/res_total
gen HPCA_A_t_hh_r_d = res_dilapidated/res_total
gen HPCA_A_t_hh_r_o = other_total/ch_total
gen HPCA_A_t_hh_rc_g = other_good/other_total
gen HPCA_A_t_hh_rc_l = other_liveable/other_total
gen HPCA_A_t_hh_rc_d = other_dilapidated/other_total

save "$hpca01/hpca01_stata/H2A_all.dta", replace
}
{
cd "$hpca01/H3A"
local files: dir "$hpca01/H3A/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total
  rename H ch_roof_grass_a
  rename I ch_roof_plastic_a
  rename J ch_roof_tiles_a
  rename K ch_roof_slate_a
  rename L ch_roof_metal_a
  rename M ch_roof_brick_a
  rename N ch_roof_stone_a
  rename O ch_roof_concrete_a
  rename P ch_roof_other_a
  destring ch_total - ch_roof_other, replace
  local state = st_code
  save "$hpca01/H3A/`state'_H3A.dta", replace
}
local statafiles: dir "$hpca01/H3A/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_COUNT_H3A = ch_total
gen HPCA_mat_r_gtbw = ch_roof_grass/ch_total
gen HPCA_mat_r_pp = ch_roof_plastic/ch_total
gen HPCA_mat_r_hmt_mmt = ch_roof_tiles/ch_total
gen HPCA_mat_r_bb = ch_roof_brick/ch_total
gen HPCA_mat_r_ss = (ch_roof_stone+ch_roof_slate)/ch_total
gen HPCA_mat_r_gmas = ch_roof_metal/ch_total
gen HPCA_mat_r_c = ch_roof_concrete/ch_total
gen HPCA_mat_r_aom = ch_roof_other/ch_total

save "$hpca01/hpca01_stata/H3A_all.dta", replace
}

{
cd "$hpca01/H3AA"
local files: dir "$hpca01/H3AA/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=7
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total_aa
  rename H ch_roof_grass_aa
  rename I ch_roof_plastic_aa
  rename J ch_roof_tiles_aa
  rename K ch_roof_slate_aa
  rename L ch_roof_metal_aa
  rename M ch_roof_brick_aa
  rename N ch_roof_stone_aa
  rename O ch_roof_concrete_aa
  rename P ch_roof_other_aa
  destring ch_total - ch_roof_other, replace
  local state = st_code
  save "$hpca01/H3AA/`state'_H3AA.dta", replace
}
local statafiles: dir "$hpca01/H3AA/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_A_COUNT_H3A = ch_total
gen HPCA_A_mat_r_gtbw = ch_roof_grass/ch_total
gen HPCA_A_mat_r_pp = ch_roof_plastic/ch_total
gen HPCA_A_mat_r_hmt_mmt = ch_roof_tiles/ch_total
gen HPCA_A_mat_r_bb = ch_roof_brick/ch_total
gen HPCA_A_mat_r_ss = (ch_roof_stone+ch_roof_slate)/ch_total
gen HPCA_A_mat_r_gmas = ch_roof_metal/ch_total
gen HPCA_A_mat_r_c = ch_roof_concrete/ch_total
gen HPCA_A_mat_r_aom = ch_roof_other/ch_total

save "$hpca01/hpca01_stata/H3AA_all.dta", replace
}

{
cd "$hpca01/H3ASCA"
local files: dir "$hpca01/H3ASCA/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=7
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total_sca
  rename H ch_roof_grass_sca
  rename I ch_roof_plastic_sca
  rename J ch_roof_tiles_sca
  rename K ch_roof_slate_sca
  rename L ch_roof_metal_sca
  rename M ch_roof_brick_sca
  rename N ch_roof_stone_sca
  rename O ch_roof_concrete_sca
  rename P ch_roof_other_sca
  destring ch_total - ch_roof_other, replace
  local state = st_code
  save "$hpca01/H3ASCA/`state'_H3ASCA.dta", replace
}
local statafiles: dir "$hpca01/H3ASCA/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_A_SC_COUNT_H3A = ch_total
gen HPCA_A_SC_mat_r_gtbw = ch_roof_grass/ch_total
gen HPCA_A_SC_mat_r_pp = ch_roof_plastic/ch_total
gen HPCA_A_SC_mat_r_hmt_mmt = ch_roof_tiles/ch_total
gen HPCA_A_SC_mat_r_bb = ch_roof_brick/ch_total
gen HPCA_A_SC_mat_r_ss = (ch_roof_stone+ch_roof_slate)/ch_total
gen HPCA_A_SC_mat_r_gmas = ch_roof_metal/ch_total
gen HPCA_A_SC_mat_r_c = ch_roof_concrete/ch_total
gen HPCA_A_SC_mat_r_aom = ch_roof_other/ch_total

save "$hpca01/hpca01_stata/H3ASCA_all.dta", replace
}

{
cd "$hpca01/H3ASTA"
local files: dir "$hpca01/H3ASTA/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=7
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total_sta
  rename H ch_roof_grass_sta
  rename I ch_roof_plastic_sta
  rename J ch_roof_tiles_sta
  rename K ch_roof_slate_sta
  rename L ch_roof_metal_sta
  rename M ch_roof_brick_sta
  rename N ch_roof_stone_sta
  rename O ch_roof_concrete_sta
  rename P ch_roof_other_sta
  destring ch_total - ch_roof_other, replace
  local state = st_code
  save "$hpca01/H3ASTA/`state'_H3ASTA.dta", replace
}
local statafiles: dir "$hpca01/H3ASTA/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_A_ST_COUNT_H3A = ch_total
gen HPCA_A_ST_mat_r_gtbw = ch_roof_grass/ch_total
gen HPCA_A_ST_mat_r_pp = ch_roof_plastic/ch_total
gen HPCA_A_ST_mat_r_hmt_mmt = ch_roof_tiles/ch_total
gen HPCA_A_ST_mat_r_bb = ch_roof_brick/ch_total
gen HPCA_A_ST_mat_r_ss = (ch_roof_stone+ch_roof_slate)/ch_total
gen HPCA_A_ST_mat_r_gmas = ch_roof_metal/ch_total
gen HPCA_A_ST_mat_r_c = ch_roof_concrete/ch_total
gen HPCA_A_ST_mat_r_aom = ch_roof_other/ch_total

save "$hpca01/hpca01_stata/H3ASTA_all.dta", replace
}

{
cd "$hpca01/H3B"
local files: dir "$hpca01/H3B/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=5
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total
  rename H ch_wall_grass
  rename I ch_wall_plastic
  rename J ch_wall_mud
  rename K ch_wall_wood
  rename L ch_wall_metal
  rename M ch_wall_brick
  rename N ch_wall_stone
  rename O ch_wall_concrete
  rename P ch_wall_other
  destring ch_total - ch_wall_other, replace
  local state = st_code
  save "$hpca01/H3B/`state'_H3B.dta", replace
}
local statafiles: dir "$hpca01/H3B/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_COUNT_H3B = ch_total
gen HPCA_mat_w_gtb = ch_wall_grass/ch_total
gen HPCA_mat_w_pp = ch_wall_plastic/ch_total
gen HPCA_mat_w_mub = ch_wall_mud/ch_total
gen HPCA_mat_w_w = ch_wall_wood/ch_total
gen HPCA_mat_w_snpwm_spwm = ch_wall_stone/ch_total
gen HPCA_mat_w_gimas = ch_wall_metal/ch_total
gen HPCA_mat_w_bb = ch_wall_brick/ch_total
gen HPCA_mat_w_c = ch_wall_concrete/ch_total
gen HPCA_mat_w_aom = ch_wall_other/ch_total

save "$hpca01/hpca01_stata/H3B_all.dta", replace
}

{
cd "$hpca01/H3BA"
local files: dir "$hpca01/H3BA/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total_a
  rename H ch_wall_grass_a
  rename I ch_wall_plastic_a
  rename J ch_wall_mud_a
  rename K ch_wall_wood_a
  rename L ch_wall_metal_a
  rename M ch_wall_brick_a
  rename N ch_wall_stone_a
  rename O ch_wall_concrete_a
  rename P ch_wall_other_a
  destring ch_total - ch_wall_other, replace
  local state = st_code
  save "$hpca01/H3BA/`state'_H3BA.dta", replace
}
local statafiles: dir "$hpca01/H3BA/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_A_COUNT_H3B = ch_total
gen HPCA_A_mat_w_gtb = ch_wall_grass/ch_total
gen HPCA_A_mat_w_pp = ch_wall_plastic/ch_total
gen HPCA_A_mat_w_mub = ch_wall_mud/ch_total
gen HPCA_A_mat_w_w = ch_wall_wood/ch_total
gen HPCA_A_mat_w_snpwm_spwm = ch_wall_stone/ch_total
gen HPCA_A_mat_w_gimas = ch_wall_metal/ch_total
gen HPCA_A_mat_w_bb = ch_wall_brick/ch_total
gen HPCA_A_mat_w_c = ch_wall_concrete/ch_total
gen HPCA_A_mat_w_aom = ch_wall_other/ch_total

save "$hpca01/hpca01_stata/H3BA_all.dta", replace
}

{
cd "$hpca01/H3BSCA"
local files: dir "$hpca01/H3BSCA/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total_sc
  rename H ch_wall_grass_sc
  rename I ch_wall_plastic_sc
  rename J ch_wall_mud_sc
  rename K ch_wall_wood_sc
  rename L ch_wall_metal_sc
  rename M ch_wall_brick_sc
  rename N ch_wall_stone_sc
  rename O ch_wall_concrete_sc
  rename P ch_wall_other_sc
  destring ch_total - ch_wall_other, replace
  local state = st_code
  save "$hpca01/H3BSCA/`state'_H3BSCA.dta", replace
}
local statafiles: dir "$hpca01/H3BSCA/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_A_SC_COUNT_H3B = ch_total
gen HPCA_A_SC_mat_w_gtb = ch_wall_grass/ch_total
gen HPCA_A_SC_mat_w_pp = ch_wall_plastic/ch_total
gen HPCA_A_SC_mat_w_mub = ch_wall_mud/ch_total
gen HPCA_A_SC_mat_w_w = ch_wall_wood/ch_total
gen HPCA_A_SC_mat_w_snpwm_spwm = ch_wall_stone/ch_total
gen HPCA_A_SC_mat_w_gimas = ch_wall_metal/ch_total
gen HPCA_A_SC_mat_w_bb = ch_wall_brick/ch_total
gen HPCA_A_SC_mat_w_c = ch_wall_concrete/ch_total
gen HPCA_A_SC_mat_w_aom = ch_wall_other/ch_total

save "$hpca01/hpca01_stata/H3BSCA_all.dta", replace
}

{
cd "$hpca01/H3BSTA"
local files: dir "$hpca01/H3BSTA/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total_st
  rename H ch_wall_grass_st
  rename I ch_wall_plastic_st
  rename J ch_wall_mud_st
  rename K ch_wall_wood_st
  rename L ch_wall_metal_st
  rename M ch_wall_brick_st
  rename N ch_wall_stone_st
  rename O ch_wall_concrete_st
  rename P ch_wall_other_st
  destring ch_total - ch_wall_other, replace
  local state = st_code
  save "$hpca01/H3BSTA/`state'_H3BSTA.dta", replace
}
local statafiles: dir "$hpca01/H3BSTA/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_A_ST_COUNT_H3B = ch_total
gen HPCA_A_ST_mat_w_gtb = ch_wall_grass/ch_total
gen HPCA_A_ST_mat_w_pp = ch_wall_plastic/ch_total
gen HPCA_A_ST_mat_w_mub = ch_wall_mud/ch_total
gen HPCA_A_ST_mat_w_w = ch_wall_wood/ch_total
gen HPCA_A_ST_mat_w_snpwm_spwm = ch_wall_stone/ch_total
gen HPCA_A_ST_mat_w_gimas = ch_wall_metal/ch_total
gen HPCA_A_ST_mat_w_bb = ch_wall_brick/ch_total
gen HPCA_A_ST_mat_w_c = ch_wall_concrete/ch_total
gen HPCA_A_ST_mat_w_aom = ch_wall_other/ch_total

save "$hpca01/hpca01_stata/H3BSTA_all.dta", replace
}

{
cd "$hpca01/H3C"
local files: dir "$hpca01/H3C/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=5
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total
  rename H ch_floor_mud
  rename I ch_floor_wood
  rename J ch_floor_brick
  rename K ch_floor_stone
  rename L ch_floor_cement
  rename M ch_floor_mosaic
  rename N ch_floor_other
  destring ch_total - ch_floor_other, replace
  local state = st_code
  save "$hpca01/H3C/`state'_H3C.dta", replace
}
local statafiles: dir "$hpca01/H3C/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_COUNT_H3C = ch_total
gen HPCA_mat_f_m = ch_floor_mud/ch_total
gen HPCA_mat_f_wb = ch_floor_wood/ch_total
gen HPCA_mat_f_bb = ch_floor_brick/ch_total
gen HPCA_mat_f_st = ch_floor_stone/ch_total
gen HPCA_mat_f_c = ch_floor_cement/ch_total
gen HPCA_mat_f_mf = ch_floor_mosaic/ch_total
gen HPCA_mat_f_aom = ch_floor_other/ch_total

save "$hpca01/hpca01_stata/H3C_all.dta", replace
}

{
cd "$hpca01/H3CA"
local files: dir "$hpca01/H3CA/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total_a
  rename H ch_floor_mud_a
  rename I ch_floor_wood_a
  rename J ch_floor_brick_a
  rename K ch_floor_stone_a
  rename L ch_floor_cement_a
  rename M ch_floor_mosaic_a
  rename N ch_floor_other_a
  destring ch_total - ch_floor_other, replace
  local state = st_code
  save "$hpca01/H3CA/`state'_H3CA.dta", replace
}
local statafiles: dir "$hpca01/H3CA/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_A_COUNT_H3C = ch_total
gen HPCA_A_mat_f_m = ch_floor_mud/ch_total
gen HPCA_A_mat_f_wb = ch_floor_wood/ch_total
gen HPCA_A_mat_f_bb = ch_floor_brick/ch_total
gen HPCA_A_mat_f_st = ch_floor_stone/ch_total
gen HPCA_A_mat_f_c = ch_floor_cement/ch_total
gen HPCA_A_mat_f_mf = ch_floor_mosaic/ch_total
gen HPCA_A_mat_f_aom = ch_floor_other/ch_total

save "$hpca01/hpca01_stata/H3CA_all.dta", replace
}

{
cd "$hpca01/H3CSTA"
local files: dir "$hpca01/H3CSTA/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total_st
  rename H ch_floor_mud_st
  rename I ch_floor_wood_st
  rename J ch_floor_brick_st
  rename K ch_floor_stone_st
  rename L ch_floor_cement_st
  rename M ch_floor_mosaic_st
  rename N ch_floor_other_st
  destring ch_total - ch_floor_other, replace
  local state = st_code
  save "$hpca01/H3CSTA/`state'_H3CSTA.dta", replace
}
local statafiles: dir "$hpca01/H3CSTA/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_A_ST_COUNT_H3C = ch_total
gen HPCA_A_ST_mat_f_m = ch_floor_mud/ch_total
gen HPCA_A_ST_mat_f_wb = ch_floor_wood/ch_total
gen HPCA_A_ST_mat_f_bb = ch_floor_brick/ch_total
gen HPCA_A_ST_mat_f_st = ch_floor_stone/ch_total
gen HPCA_A_ST_mat_f_c = ch_floor_cement/ch_total
gen HPCA_A_ST_mat_f_mf = ch_floor_mosaic/ch_total
gen HPCA_A_ST_mat_f_aom = ch_floor_other/ch_total

save "$hpca01/hpca01_stata/H3CSTA_all.dta", replace
}

{
cd "$hpca01/H3CSCA"
local files: dir "$hpca01/H3CSCA/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total_sc
  rename H ch_floor_mud_sc
  rename I ch_floor_wood_sc
  rename J ch_floor_brick_sc
  rename K ch_floor_stone_sc
  rename L ch_floor_cement_sc
  rename M ch_floor_mosaic_sc
  rename N ch_floor_other_sc
  destring ch_total - ch_floor_other, replace
  local state = st_code
  save "$hpca01/H3CSCA/`state'_H3CSCA.dta", replace
}
local statafiles: dir "$hpca01/H3CSCA/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_A_SC_COUNT_H3C = ch_total
gen HPCA_A_SC_mat_f_m = ch_floor_mud/ch_total
gen HPCA_A_SC_mat_f_wb = ch_floor_wood/ch_total
gen HPCA_A_SC_mat_f_bb = ch_floor_brick/ch_total
gen HPCA_A_SC_mat_f_st = ch_floor_stone/ch_total
gen HPCA_A_SC_mat_f_c = ch_floor_cement/ch_total
gen HPCA_A_SC_mat_f_mf = ch_floor_mosaic/ch_total
gen HPCA_A_SC_mat_f_aom = ch_floor_other/ch_total

save "$hpca01/hpca01_stata/H3CSCA_all.dta", replace
}


{
cd "$hpca01/H4"
local files: dir "$hpca01/H4/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total
  rename H ch_permanent
  rename I ch_semiperm
  rename J ch_temp_total
  rename K ch_temp_servicable
  rename L ch_temp_nonservicable
  rename M ch_unclassifiable
  destring ch_total - ch_unclassifiable, replace
  local state = st_code
  save "$hpca01/H4/`state'_H4.dta", replace
}
local statafiles: dir "$hpca01/H4/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_COUNT_H4 = ch_total
gen HPCA_hh_perm = ch_permanent/ch_total
gen HPCA_hh_semi_perm = ch_semiperm/ch_total
gen HPCA_hh_temp_total = ch_temp_total/ch_total
gen HPCA_hh_temp_serv = ch_temp_servicable/ch_total
gen HPCA_hh_temp_nonserv = ch_temp_nonservicable/ch_total
gen HPCA_hh_unclassif = ch_unclassifiable/ch_total

save "$hpca01/hpca01_stata/H4_all.dta", replace
}

{
cd "$hpca01/H4A"
local files: dir "$hpca01/H4A/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total
  rename H ch_permanent_a
  rename I ch_semiperm_a
  rename J ch_temp_total_a
  rename K ch_temp_servicable_a
  rename L ch_temp_nonservicable_a
  rename M ch_unclassifiable_a
  destring ch_total - ch_unclassifiable, replace
  local state = st_code
  save "$hpca01/H4A/`state'_H4A.dta", replace
}
local statafiles: dir "$hpca01/H4A/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_A_COUNT_H4 = ch_total
gen HPCA_A_hh_perm = ch_permanent/ch_total
gen HPCA_A_hh_semi_perm = ch_semiperm/ch_total
gen HPCA_A_hh_temp_total = ch_temp_total/ch_total
gen HPCA_A_hh_temp_serv = ch_temp_servicable/ch_total
gen HPCA_A_hh_temp_nonserv = ch_temp_nonservicable/ch_total
gen HPCA_A_hh_unclassif = ch_unclassifiable/ch_total

save "$hpca01/hpca01_stata/H4A_all.dta", replace
}

{
cd "$hpca01/H4SCA"
local files: dir "$hpca01/H4SCA/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total
  rename H ch_permanent_sc
  rename I ch_semiperm_sc
  rename J ch_temp_total_sc
  rename K ch_temp_servicable_sc
  rename L ch_temp_nonservicable_sc
  rename M ch_unclassifiable_sc
  destring ch_total - ch_unclassifiable, replace
  local state = st_code
  save "$hpca01/H4SCA/`state'_H4SCA.dta", replace
}
local statafiles: dir "$hpca01/H4SCA/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_A_SC_COUNT_H4 = ch_total
gen HPCA_A_SC_hh_perm = ch_permanent/ch_total
gen HPCA_A_SC_hh_semi_perm = ch_semiperm/ch_total
gen HPCA_A_SC_hh_temp_total = ch_temp_total/ch_total
gen HPCA_A_SC_hh_temp_serv = ch_temp_servicable/ch_total
gen HPCA_A_SC_hh_temp_nonserv = ch_temp_nonservicable/ch_total
gen HPCA_A_SC_hh_unclassif = ch_unclassifiable/ch_total

save "$hpca01/hpca01_stata/H4SCA_all.dta", replace
}

{
cd "$hpca01/H4STA"
local files: dir "$hpca01/H4STA/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total
  rename H ch_permanent_st
  rename I ch_semiperm_st
  rename J ch_temp_total_st
  rename K ch_temp_servicable_st
  rename L ch_temp_nonservicable_st
  rename M ch_unclassifiable_st
  destring ch_total - ch_unclassifiable, replace
  local state = st_code
  save "$hpca01/H4STA/`state'_H4STA.dta", replace
}
local statafiles: dir "$hpca01/H4STA/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_A_ST_COUNT_H4 = ch_total
gen HPCA_A_ST_hh_perm = ch_permanent/ch_total
gen HPCA_A_ST_hh_semi_perm = ch_semiperm/ch_total
gen HPCA_A_ST_hh_temp_total = ch_temp_total/ch_total
gen HPCA_A_ST_hh_temp_serv = ch_temp_servicable/ch_total
gen HPCA_A_ST_hh_temp_nonserv = ch_temp_nonservicable/ch_total
gen HPCA_A_ST_hh_unclassif = ch_unclassifiable/ch_total

save "$hpca01/hpca01_stata/H4STA_all.dta", replace
}

{
cd "$hpca01/H5"
local files: dir "$hpca01/H5/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=5
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_size
  rename H ch_total
  rename I ch_room_noexcl
  rename J ch_room_one
  rename K ch_room_two
  rename L ch_room_three
  rename M ch_room_four
  rename N ch_room_five
  rename O ch_room_sixplus
  rename P ch_room_mediannr
  destring ch_total - ch_room_mediannr, replace
  local state = st_code
  save "$hpca01/H5/`state'_H5.dta", replace
}
local statafiles: dir "$hpca01/H5/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_size="TOT" if ch_size=="All Households"
replace ch_size="6" if ch_size=="6-8"
replace ch_size="9" if ch_size=="9+"
drop if inlist(ch_size,"TOT","1","2","3","4","5","6","9")==0 
reshape wide ch_total-ch_room_mediannr, i(st_code-rural_urban) j(ch_size) string

gen HPCA_COUNT_H5 = ch_totalTOT
gen HPCA_room_0 = ch_room_noexclTOT/ch_totalTOT
gen HPCA_room_1 = ch_room_oneTOT/ch_totalTOT
gen HPCA_room_2 = ch_room_twoTOT/ch_totalTOT
gen HPCA_room_3 = ch_room_threeTOT/ch_totalTOT
gen HPCA_room_4 = ch_room_fourTOT/ch_totalTOT
gen HPCA_room_5 = ch_room_fiveTOT/ch_totalTOT
gen HPCA_room_6plus = ch_room_sixplusTOT/ch_totalTOT
gen HPCA_h_size_1 = ch_total1/ch_totalTOT
gen HPCA_h_size_2 = ch_total2/ch_totalTOT
gen HPCA_h_size_3 = ch_total3/ch_totalTOT
gen HPCA_h_size_4 = ch_total4/ch_totalTOT
gen HPCA_h_size_5 = ch_total5/ch_totalTOT
gen HPCA_h_size_6_8 = ch_total6/ch_totalTOT
gen HPCA_h_size_9plus = ch_total9/ch_totalTOT

save "$hpca01/hpca01_stata/H5_all.dta", replace
}

{
cd "$hpca01/H5ST"
local files: dir "$hpca01/H5ST/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=5
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_size_st
  rename H ch_total_st
  rename I ch_room_noexcl_st
  rename J ch_room_one_st
  rename K ch_room_two_st
  rename L ch_room_three_st
  rename M ch_room_four_st
  rename N ch_room_five_st
  rename O ch_room_sixplus_st
  rename P ch_room_mediannr_st
  destring ch_total - ch_room_mediannr, replace
  local state = st_code
  save "$hpca01/H5ST/`state'_H5ST.dta", replace
}
local statafiles: dir "$hpca01/H5ST/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_size_st="TOT" if ch_size_st=="All Households"
replace ch_size_st="6" if ch_size_st=="6-8"
replace ch_size_st="9" if ch_size_st=="9+"
drop if inlist(ch_size_st,"TOT","1","2","3","4","5","6","9")==0 
reshape wide ch_total_st-ch_room_mediannr_st, i(st_code-rural_urban) j(ch_size_st) string

gen HPCA_ST_COUNT_H5 = ch_total_stTOT
gen HPCA_ST_room_0 = ch_room_noexcl_stTOT/ch_total_stTOT
gen HPCA_ST_room_1 = ch_room_one_stTOT/ch_total_stTOT
gen HPCA_ST_room_2 = ch_room_two_stTOT/ch_total_stTOT
gen HPCA_ST_room_3 = ch_room_three_stTOT/ch_total_stTOT
gen HPCA_ST_room_4 = ch_room_four_stTOT/ch_total_stTOT
gen HPCA_ST_room_5 = ch_room_five_stTOT/ch_total_stTOT
gen HPCA_ST_room_6plus = ch_room_sixplus_stTOT/ch_total_stTOT
gen HPCA_ST_h_size_1 = ch_total_st1/ch_total_stTOT
gen HPCA_ST_h_size_2 = ch_total_st2/ch_total_stTOT
gen HPCA_ST_h_size_3 = ch_total_st3/ch_total_stTOT
gen HPCA_ST_h_size_4 = ch_total_st4/ch_total_stTOT
gen HPCA_ST_h_size_5 = ch_total_st5/ch_total_stTOT
gen HPCA_ST_h_size_6_8 = ch_total_st6/ch_total_stTOT
gen HPCA_ST_h_size_9plus = ch_total_st9/ch_total_stTOT

save "$hpca01/hpca01_stata/H5ST_all.dta", replace
}

{
cd "$hpca01/H5SC"
local files: dir "$hpca01/H5SC/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=5
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_size_sc
  rename H ch_total_sc
  rename I ch_room_noexcl_sc
  rename J ch_room_one_sc
  rename K ch_room_two_sc
  rename L ch_room_three_sc
  rename M ch_room_four_sc
  rename N ch_room_five_sc
  rename O ch_room_sixplus_sc
  rename P ch_room_mediannr_sc
  destring ch_total - ch_room_mediannr, replace
  local state = st_code
  save "$hpca01/H5SC/`state'_H5SC.dta", replace
}
local statafiles: dir "$hpca01/H5SC/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_size_sc="TOT" if ch_size_sc=="All Households"
replace ch_size_sc="6" if ch_size_sc=="6-8"
replace ch_size_sc="9" if ch_size_sc=="9+"
drop if inlist(ch_size_sc,"TOT","1","2","3","4","5","6","9")==0 
reshape wide ch_total_sc-ch_room_mediannr_sc, i(st_code-rural_urban) j(ch_size_sc) string

gen HPCA_SC_COUNT_H5 = ch_total_scTOT
gen HPCA_SC_room_0 = ch_room_noexcl_scTOT/ch_total_scTOT
gen HPCA_SC_room_1 = ch_room_one_scTOT/ch_total_scTOT
gen HPCA_SC_room_2 = ch_room_two_scTOT/ch_total_scTOT
gen HPCA_SC_room_3 = ch_room_three_scTOT/ch_total_scTOT
gen HPCA_SC_room_4 = ch_room_four_scTOT/ch_total_scTOT
gen HPCA_SC_room_5 = ch_room_five_scTOT/ch_total_scTOT
gen HPCA_SC_room_6plus = ch_room_sixplus_scTOT/ch_total_scTOT
gen HPCA_SC_h_size_1 = ch_total_sc1/ch_total_scTOT
gen HPCA_SC_h_size_2 = ch_total_sc2/ch_total_scTOT
gen HPCA_SC_h_size_3 = ch_total_sc3/ch_total_scTOT
gen HPCA_SC_h_size_4 = ch_total_sc4/ch_total_scTOT
gen HPCA_SC_h_size_5 = ch_total_sc5/ch_total_scTOT
gen HPCA_SC_h_size_6_8 = ch_total_sc6/ch_total_scTOT
gen HPCA_SC_h_size_9plus = ch_total_sc9/ch_total_scTOT

save "$hpca01/hpca01_stata/H5SC_all.dta", replace
}


{
cd "$hpca01/H6"
local files: dir "$hpca01/H6/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_ownership
  rename H ch_total
  rename I ch_room_noexcl
  rename J ch_room_one
  rename K ch_room_two
  rename L ch_room_three
  rename M ch_room_four
  rename N ch_room_five
  rename O ch_room_sixplus
  rename P ch_room_mediannr
  destring ch_total - ch_room_mediannr, replace
  local state = st_code
  save "$hpca01/H6/`state'_H6.dta", replace
}
local statafiles: dir "$hpca01/H6/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_ownership="TOT" if ch_ownership=="Total"
replace ch_ownership="OWN" if ch_ownership=="Owned"
replace ch_ownership="RENT" if ch_ownership=="Rented"
replace ch_ownership="OT" if ch_ownership=="Any other"
drop if inlist(ch_ownership,"TOT","OWN","RENT","OT")==0 
reshape wide ch_total-ch_room_mediannr, i(st_code-rural_urban) j(ch_ownership) string

gen HPCA_COUNT_H6 = ch_totalTOT
gen HPCA_owned = ch_totalOWN/ch_totalTOT
gen HPCA_rented = ch_totalRENT/ch_totalTOT
gen HPCA_not_owned_rented = ch_totalOT/ch_totalTOT

save "$hpca01/hpca01_stata/H6_all.dta", replace
}

{
cd "$hpca01/H6ST"
local files: dir "$hpca01/H6ST/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_ownership_st
  rename H ch_total_st
  rename I ch_room_noexcl_st
  rename J ch_room_one_st
  rename K ch_room_two_st
  rename L ch_room_three_st
  rename M ch_room_four_st
  rename N ch_room_five_st
  rename O ch_room_sixplus_st
  rename P ch_room_mediannr_st
  destring ch_total - ch_room_mediannr, replace
  local state = st_code
  save "$hpca01/H6ST/`state'_H6ST.dta", replace
}
local statafiles: dir "$hpca01/H6ST/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_ownership_st="TOT" if ch_ownership_st=="Total"
replace ch_ownership_st="OWN" if ch_ownership_st=="Owned"
replace ch_ownership_st="RENT" if ch_ownership_st=="Rented"
replace ch_ownership_st="OT" if ch_ownership_st=="Any other"
drop if inlist(ch_ownership_st,"TOT","OWN","RENT","OT")==0 
reshape wide ch_total_st-ch_room_mediannr_st, i(st_code-rural_urban) j(ch_ownership_st) string

gen HPCA_ST_COUNT_H6 = ch_total_stTOT
gen HPCA_ST_owned = ch_total_stOWN/ch_total_stTOT
gen HPCA_ST_rented = ch_total_stRENT/ch_total_stTOT
gen HPCA_ST_not_owned_rented = ch_total_stOT/ch_total_stTOT

save "$hpca01/hpca01_stata/H6ST_all.dta", replace
}

{
cd "$hpca01/H6SC"
local files: dir "$hpca01/H6SC/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_ownership_sc
  rename H ch_total_sc
  rename I ch_room_noexcl_sc
  rename J ch_room_one_sc
  rename K ch_room_two_sc
  rename L ch_room_three_sc
  rename M ch_room_four_sc
  rename N ch_room_five_sc
  rename O ch_room_sixplus_sc
  rename P ch_room_mediannr_sc
  destring ch_total - ch_room_mediannr, replace
  local state = st_code
  save "$hpca01/H6SC/`state'_H6SC.dta", replace
}
local statafiles: dir "$hpca01/H6SC/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_ownership_sc="TOT" if ch_ownership_sc=="Total"
replace ch_ownership_sc="OWN" if ch_ownership_sc=="Owned"
replace ch_ownership_sc="RENT" if ch_ownership_sc=="Rented"
replace ch_ownership_sc="OT" if ch_ownership_sc=="Any other"
drop if inlist(ch_ownership_sc,"TOT","OWN","RENT","OT")==0 
reshape wide ch_total_sc-ch_room_mediannr_sc, i(st_code-rural_urban) j(ch_ownership_sc) string

gen HPCA_SC_COUNT_H6 = ch_total_scTOT
gen HPCA_SC_owned = ch_total_scOWN/ch_total_scTOT
gen HPCA_SC_rented = ch_total_scRENT/ch_total_scTOT
gen HPCA_SC_not_owned_rented = ch_total_scOT/ch_total_scTOT

save "$hpca01/hpca01_stata/H6SC_all.dta", replace
}

{
cd "$hpca01/H6A"
local files: dir "$hpca01/H6A/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_ownership_a
  rename H ch_size_a
  rename I ch_total_a
  rename J ch_room_noexcl_a
  rename K ch_room_one_a
  rename L ch_room_two_a
  rename M ch_room_three_a
  rename N ch_room_four_a
  rename O ch_room_five_a
  rename P ch_room_sixplus_a
  destring ch_total - ch_room_sixplus, replace
  local state = st_code
  save "$hpca01/H6A/`state'_H6A.dta", replace
}
local statafiles: dir "$hpca01/H6A/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_ownership_a="TOT" if ch_ownership_a=="Total"
replace ch_ownership_a="OWN" if ch_ownership_a=="Owned"
replace ch_ownership_a="RENT" if ch_ownership_a=="Rented"
replace ch_ownership_a="OT" if ch_ownership_a=="Any other"
replace ch_size_a="TOT" if ch_size_a=="All Households"
replace ch_size_a="6" if ch_size_a=="6-8"
replace ch_size_a="9" if ch_size_a=="9+"
drop if inlist(ch_ownership_a,"TOT","OWN","RENT","OT")==0 | inlist(ch_size_a,"TOT","1","2","3","4","5","6","9")==0
gen j = ch_ownership_a + "_" + ch_size_a
drop ch_ownership_a ch_size_a
reshape wide ch_total_a-ch_room_sixplus_a, i(st_code-rural_urban) j(j) string

gen HPCA_A_COUNT_H6 = ch_total_aTOT_TOT
gen HPCA_A_owned = ch_total_aOWN_TOT/ch_total_aTOT_TOT
gen HPCA_A_rented = ch_total_aRENT_TOT/ch_total_aTOT_TOT
gen HPCA_A_not_owned_rented = ch_total_aOT_TOT/ch_total_aTOT_TOT

save "$hpca01/hpca01_stata/H6A_all.dta", replace
}


{
cd "$hpca01/H6ASC"
local files: dir "$hpca01/H6ASC/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_ownership_sca
  rename H ch_size_sca
  rename I ch_total_sca
  rename J ch_room_noexcl_sca
  rename K ch_room_one_sca
  rename L ch_room_two_sca
  rename M ch_room_three_sca
  rename N ch_room_four_sca
  rename O ch_room_five_sca
  rename P ch_room_sixplus_sca
  destring ch_total - ch_room_sixplus, replace
  local state = st_code
  save "$hpca01/H6ASC/`state'_H6ASC.dta", replace
}
local statafiles: dir "$hpca01/H6ASC/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_ownership_sca="TOT" if ch_ownership_sca=="Total"
replace ch_ownership_sca="OWN" if ch_ownership_sca=="Owned"
replace ch_ownership_sca="RENT" if ch_ownership_sca=="Rented"
replace ch_ownership_sca="OT" if ch_ownership_sca=="Any other"
replace ch_size_sca="TOT" if ch_size_sca=="All Households"
replace ch_size_sca="6" if ch_size_sca=="6-8"
replace ch_size_sca="9" if ch_size_sca=="9+"
drop if inlist(ch_ownership_sca,"TOT","OWN","RENT","OT")==0 | inlist(ch_size_sca,"TOT","1","2","3","4","5","6","9")==0
gen j = ch_ownership_sca + "_" + ch_size_sca
drop ch_ownership_sca ch_size_sca
reshape wide ch_total_sca-ch_room_sixplus_sca, i(st_code-rural_urban) j(j) string

gen HPCA_A_SC_COUNT_H6 = ch_total_scaTOT_TOT
gen HPCA_A_SC_owned = ch_total_scaOWN_TOT/ch_total_scaTOT_TOT
gen HPCA_A_SC_rented = ch_total_scaRENT_TOT/ch_total_scaTOT_TOT
gen HPCA_A_SC_not_owned_rented = ch_total_scaOT_TOT/ch_total_scaTOT_TOT

save "$hpca01/hpca01_stata/H6ASC_all.dta", replace
}

{
cd "$hpca01/H6AST"
local files: dir "$hpca01/H6AST/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_ownership_sta
  rename H ch_size_sta
  rename I ch_total_sta
  rename J ch_room_noexcl_sta
  rename K ch_room_one_sta
  rename L ch_room_two_sta
  rename M ch_room_three_sta
  rename N ch_room_four_sta
  rename O ch_room_five_sta
  rename P ch_room_sixplus_sta
  destring ch_total - ch_room_sixplus, replace
  local state = st_code
  save "$hpca01/H6AST/`state'_H6AST.dta", replace
}
local statafiles: dir "$hpca01/H6AST/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_ownership_sta="TOT" if ch_ownership_sta=="Total"
replace ch_ownership_sta="OWN" if ch_ownership_sta=="Owned"
replace ch_ownership_sta="RENT" if ch_ownership_sta=="Rented"
replace ch_ownership_sta="OT" if ch_ownership_sta=="Any other"
replace ch_size_sta="TOT" if ch_size_sta=="All Households"
replace ch_size_sta="6" if ch_size_sta=="6-8"
replace ch_size_sta="9" if ch_size_sta=="9+"
drop if inlist(ch_ownership_sta,"TOT","OWN","RENT","OT")==0 | inlist(ch_size_sta,"TOT","1","2","3","4","5","6","9")==0
gen j = ch_ownership_sta + "_" + ch_size_sta
drop ch_ownership_sta ch_size_sta
reshape wide ch_total_sta-ch_room_sixplus_sta, i(st_code-rural_urban) j(j) string

gen HPCA_A_ST_COUNT_H6 = ch_total_staTOT_TOT
gen HPCA_A_ST_owned = ch_total_staOWN_TOT/ch_total_staTOT_TOT
gen HPCA_A_ST_rented = ch_total_staRENT_TOT/ch_total_staTOT_TOT
gen HPCA_A_ST_not_owned_rented = ch_total_staOT_TOT/ch_total_staTOT_TOT

save "$hpca01/hpca01_stata/H6AST_all.dta", replace
}


{
cd "$hpca01/H7"
local files: dir "$hpca01/H7/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=8
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_nr_marr_cpls
  rename H ch_total
  rename I ch_room_noexcl
  rename J ch_room_one
  rename K ch_room_two
  rename L ch_room_three
  rename M ch_room_four
  rename N ch_room_fiveplus
  rename O total_nr_marr_cpls
  rename P total_nr_marr_cpls_ir
  destring ch_total - total_nr_marr_cpls_ir, replace
  local state = st_code
  save "$hpca01/H7/`state'_H7.dta", replace
}
local statafiles: dir "$hpca01/H7/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_nr_marr_cpls="marr0" if ch_nr_marr_cpls=="None"
replace ch_nr_marr_cpls="marr1" if ch_nr_marr_cpls=="1"
replace ch_nr_marr_cpls="marr2" if ch_nr_marr_cpls=="2"
replace ch_nr_marr_cpls="marr3" if ch_nr_marr_cpls=="3"
replace ch_nr_marr_cpls="marr4" if ch_nr_marr_cpls=="4"
replace ch_nr_marr_cpls="marr5" if ch_nr_marr_cpls=="5+"
replace ch_nr_marr_cpls="marrTOT" if ch_nr_marr_cpls=="Total"
drop if inlist(ch_nr_marr_cpls,"marr0","marr1","marr2","marr3","marr4","marr5","marrTOT")==0 
reshape wide ch_total-total_nr_marr_cpls_ir, i(st_code-rural_urban) j(ch_nr_marr_cpls) string

gen HPCA_COUNT_H7 = ch_totalmarrTOT
gen HPCA_coup_0 = ch_totalmarr0/ch_totalmarrTOT
gen HPCA_coup_1 = ch_totalmarr1/ch_totalmarrTOT
gen HPCA_coup_2 = ch_totalmarr2/ch_totalmarrTOT
gen HPCA_coup_3 = ch_totalmarr3/ch_totalmarrTOT
gen HPCA_coup_4 = ch_totalmarr4/ch_totalmarrTOT
gen HPCA_coup_5 = ch_totalmarr5/ch_totalmarrTOT

save "$hpca01/hpca01_stata/H7_all.dta", replace
}

{
cd "$hpca01/H7ST"
local files: dir "$hpca01/H7ST/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=8
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_nr_marr_cpls_st
  rename H ch_total_st
  rename I ch_room_noexcl_st
  rename J ch_room_one_st
  rename K ch_room_two_st
  rename L ch_room_three_st
  rename M ch_room_four_st
  rename N ch_room_fiveplus_st
  rename O total_nr_marr_cpls_st
  rename P total_nr_marr_cpls_ir_st
  destring ch_total - total_nr_marr_cpls_ir, replace
  local state = st_code
  save "$hpca01/H7ST/`state'_H7ST.dta", replace
}
local statafiles: dir "$hpca01/H7ST/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_nr_marr_cpls_st="marr0" if ch_nr_marr_cpls_st=="None"
replace ch_nr_marr_cpls_st="marr1" if ch_nr_marr_cpls_st=="1"
replace ch_nr_marr_cpls_st="marr2" if ch_nr_marr_cpls_st=="2"
replace ch_nr_marr_cpls_st="marr3" if ch_nr_marr_cpls_st=="3"
replace ch_nr_marr_cpls_st="marr4" if ch_nr_marr_cpls_st=="4"
replace ch_nr_marr_cpls_st="marr5" if ch_nr_marr_cpls_st=="5+"
replace ch_nr_marr_cpls_st="marrTOT" if ch_nr_marr_cpls_st=="Total"
drop if inlist(ch_nr_marr_cpls_st,"marr0","marr1","marr2","marr3","marr4","marr5","marrTOT")==0 
reshape wide ch_total_st-total_nr_marr_cpls_ir_st, i(st_code-rural_urban) j(ch_nr_marr_cpls_st) string

gen HPCA_ST_COUNT_H7 = ch_total_stmarrTOT
gen HPCA_ST_coup_0 = ch_total_stmarr0/ch_total_stmarrTOT
gen HPCA_ST_coup_1 = ch_total_stmarr1/ch_total_stmarrTOT
gen HPCA_ST_coup_2 = ch_total_stmarr2/ch_total_stmarrTOT
gen HPCA_ST_coup_3 = ch_total_stmarr3/ch_total_stmarrTOT
gen HPCA_ST_coup_4 = ch_total_stmarr4/ch_total_stmarrTOT
gen HPCA_ST_coup_5 = ch_total_stmarr5/ch_total_stmarrTOT

save "$hpca01/hpca01_stata/H7ST_all.dta", replace
}

{
cd "$hpca01/H7SC"
local files: dir "$hpca01/H7SC/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=8
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_nr_marr_cpls_sc
  rename H ch_total_sc
  rename I ch_room_noexcl_sc
  rename J ch_room_one_sc
  rename K ch_room_two_sc
  rename L ch_room_three_sc
  rename M ch_room_four_sc
  rename N ch_room_fiveplus_sc
  rename O total_nr_marr_cpls_sc
  rename P total_nr_marr_cpls_ir_sc
  destring ch_total - total_nr_marr_cpls_ir, replace
  local state = st_code
  save "$hpca01/H7SC/`state'_H7SC.dta", replace
}
local statafiles: dir "$hpca01/H7SC/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_nr_marr_cpls_sc="marr0" if ch_nr_marr_cpls_sc=="None"
replace ch_nr_marr_cpls_sc="marr1" if ch_nr_marr_cpls_sc=="1"
replace ch_nr_marr_cpls_sc="marr2" if ch_nr_marr_cpls_sc=="2"
replace ch_nr_marr_cpls_sc="marr3" if ch_nr_marr_cpls_sc=="3"
replace ch_nr_marr_cpls_sc="marr4" if ch_nr_marr_cpls_sc=="4"
replace ch_nr_marr_cpls_sc="marr5" if ch_nr_marr_cpls_sc=="5+"
replace ch_nr_marr_cpls_sc="marrTOT" if ch_nr_marr_cpls_sc=="Total"
drop if inlist(ch_nr_marr_cpls_sc,"marr0","marr1","marr2","marr3","marr4","marr5","marrTOT")==0 
reshape wide ch_total_sc-total_nr_marr_cpls_ir_sc, i(st_code-rural_urban) j(ch_nr_marr_cpls_sc) string

gen HPCA_SC_COUNT_H7 = ch_total_scmarrTOT
gen HPCA_SC_coup_0 = ch_total_scmarr0/ch_total_scmarrTOT
gen HPCA_SC_coup_1 = ch_total_scmarr1/ch_total_scmarrTOT
gen HPCA_SC_coup_2 = ch_total_scmarr2/ch_total_scmarrTOT
gen HPCA_SC_coup_3 = ch_total_scmarr3/ch_total_scmarrTOT
gen HPCA_SC_coup_4 = ch_total_scmarr4/ch_total_scmarrTOT
gen HPCA_SC_coup_5 = ch_total_scmarr5/ch_total_scmarrTOT

save "$hpca01/hpca01_stata/H7SC_all.dta", replace
}

{
cd "$hpca01/H7A"
local files: dir "$hpca01/H7A/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=12
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_nr_marr_cpls_a
  rename H ch_total_a
	rename I ch_total_ir_a
  rename J ch_room_noexcl_a
  rename K ch_room_one_a
  rename L ch_room_one_ir_a
  rename M ch_room_two_a
  rename N ch_room_two_ir_a
  rename O ch_room_three_a
  rename P ch_room_three_ir_a
  rename Q ch_room_four_a
  rename R ch_room_four_ir_a
  rename S ch_room_fiveplus_a
  rename T ch_room_fiveplus_ir_a
  destring ch_total_a - ch_room_fiveplus_ir, replace
  local state = st_code
  save "$hpca01/H7A/`state'_H7A.dta", replace
}
local statafiles: dir "$hpca01/H7A/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_nr_marr_cpls_a="marr0" if ch_nr_marr_cpls_a=="None"
replace ch_nr_marr_cpls_a="marr1" if ch_nr_marr_cpls_a=="1"
replace ch_nr_marr_cpls_a="marr2" if ch_nr_marr_cpls_a=="2"
replace ch_nr_marr_cpls_a="marr3" if ch_nr_marr_cpls_a=="3"
replace ch_nr_marr_cpls_a="marr4" if ch_nr_marr_cpls_a=="4"
replace ch_nr_marr_cpls_a="marr5" if ch_nr_marr_cpls_a=="5+"
replace ch_nr_marr_cpls_a="marrTOT" if ch_nr_marr_cpls_a=="Total"
drop if inlist(ch_nr_marr_cpls_a,"marr0","marr1","marr2","marr3","marr4","marr5","marrTOT")==0 
reshape wide ch_total_a-ch_room_fiveplus_ir_a, i(st_code-rural_urban) j(ch_nr_marr_cpls_a) string

gen HPCA_A_COUNT_H7 = ch_total_amarrTOT
cap gen HPCA_A_coup_0 = ch_total_amarr0/ch_total_amarrTOT
gen HPCA_A_coup_1 = ch_total_amarr1/ch_total_amarrTOT
gen HPCA_A_coup_2 = ch_total_amarr2/ch_total_amarrTOT
gen HPCA_A_coup_3 = ch_total_amarr3/ch_total_amarrTOT
gen HPCA_A_coup_4 = ch_total_amarr4/ch_total_amarrTOT
gen HPCA_A_coup_5 = ch_total_amarr5/ch_total_amarrTOT

save "$hpca01/hpca01_stata/H7A_all.dta", replace
}

{
cd "$hpca01/H7AST"
local files: dir "$hpca01/H7AST/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=12
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_nr_marr_cpls_sta
  rename H ch_total_sta
	rename I ch_total_ir_sta
  rename J ch_room_noexcl_sta
  rename K ch_room_one_sta
  rename L ch_room_one_ir_sta
  rename M ch_room_two_sta
  rename N ch_room_two_ir_sta
  rename O ch_room_three_sta
  rename P ch_room_three_ir_sta
  rename Q ch_room_four_sta
  rename R ch_room_four_ir_sta
  rename S ch_room_fiveplus_sta
  rename T ch_room_fiveplus_ir_sta
  destring ch_total_sta - ch_room_fiveplus_ir, replace
  local state = st_code
  save "$hpca01/H7AST/`state'_H7AST.dta", replace
}
local statafiles: dir "$hpca01/H7AST/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_nr_marr_cpls_sta="marr0" if ch_nr_marr_cpls_sta=="None"
replace ch_nr_marr_cpls_sta="marr1" if ch_nr_marr_cpls_sta=="1"
replace ch_nr_marr_cpls_sta="marr2" if ch_nr_marr_cpls_sta=="2"
replace ch_nr_marr_cpls_sta="marr3" if ch_nr_marr_cpls_sta=="3"
replace ch_nr_marr_cpls_sta="marr4" if ch_nr_marr_cpls_sta=="4"
replace ch_nr_marr_cpls_sta="marr5" if ch_nr_marr_cpls_sta=="5+"
replace ch_nr_marr_cpls_sta="marrTOT" if ch_nr_marr_cpls_sta=="Total"
drop if inlist(ch_nr_marr_cpls_sta,"marr0","marr1","marr2","marr3","marr4","marr5","marrTOT")==0 
reshape wide ch_total_sta-ch_room_fiveplus_ir_sta, i(st_code-rural_urban) j(ch_nr_marr_cpls_sta) string

gen HPCA_A_ST_COUNT_H7 = ch_total_stamarrTOT
cap gen HPCA_A_ST_coup_0 = ch_total_stamarr0/ch_total_stamarrTOT
gen HPCA_A_ST_coup_1 = ch_total_stamarr1/ch_total_stamarrTOT
gen HPCA_A_ST_coup_2 = ch_total_stamarr2/ch_total_stamarrTOT
gen HPCA_A_ST_coup_3 = ch_total_stamarr3/ch_total_stamarrTOT
gen HPCA_A_ST_coup_4 = ch_total_stamarr4/ch_total_stamarrTOT
gen HPCA_A_ST_coup_5 = ch_total_stamarr5/ch_total_stamarrTOT

save "$hpca01/hpca01_stata/H7AST_all.dta", replace
}

{
cd "$hpca01/H7ASC"
local files: dir "$hpca01/H7ASC/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=12
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_nr_marr_cpls_sca
  rename H ch_total_sca
	rename I ch_total_ir_sca
  rename J ch_room_noexcl_sca
  rename K ch_room_one_sca
  rename L ch_room_one_ir_sca
  rename M ch_room_two_sca
  rename N ch_room_two_ir_sca
  rename O ch_room_three_sca
  rename P ch_room_three_ir_sca
  rename Q ch_room_four_sca
  rename R ch_room_four_ir_sca
  rename S ch_room_fiveplus_sca
  rename T ch_room_fiveplus_ir_sca
  destring ch_total_sca - ch_room_fiveplus_ir, replace
  local state = st_code
  save "$hpca01/H7ASC/`state'_H7ASC.dta", replace
}
local statafiles: dir "$hpca01/H7ASC/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_nr_marr_cpls_sca="marr0" if ch_nr_marr_cpls_sca=="None"
replace ch_nr_marr_cpls_sca="marr1" if ch_nr_marr_cpls_sca=="1"
replace ch_nr_marr_cpls_sca="marr2" if ch_nr_marr_cpls_sca=="2"
replace ch_nr_marr_cpls_sca="marr3" if ch_nr_marr_cpls_sca=="3"
replace ch_nr_marr_cpls_sca="marr4" if ch_nr_marr_cpls_sca=="4"
replace ch_nr_marr_cpls_sca="marr5" if ch_nr_marr_cpls_sca=="5+"
replace ch_nr_marr_cpls_sca="marrTOT" if ch_nr_marr_cpls_sca=="Total"
drop if inlist(ch_nr_marr_cpls_sca,"marr0","marr1","marr2","marr3","marr4","marr5","marrTOT")==0 
reshape wide ch_total_sca-ch_room_fiveplus_ir_sca, i(st_code-rural_urban) j(ch_nr_marr_cpls_sca) string

gen HPCA_A_SC_COUNT_H7 = ch_total_scamarrTOT
cap gen HPCA_A_SC_coup_0 = ch_total_scamarr0/ch_total_scamarrTOT
gen HPCA_A_SC_coup_1 = ch_total_scamarr1/ch_total_scamarrTOT
gen HPCA_A_SC_coup_2 = ch_total_scamarr2/ch_total_scamarrTOT
gen HPCA_A_SC_coup_3 = ch_total_scamarr3/ch_total_scamarrTOT
gen HPCA_A_SC_coup_4 = ch_total_scamarr4/ch_total_scamarrTOT
gen HPCA_A_SC_coup_5 = ch_total_scamarr5/ch_total_scamarrTOT

save "$hpca01/hpca01_stata/H7ASC_all.dta", replace
}

{
cd "$hpca01/H8"
local files: dir "$hpca01/H8/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=5
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_water_loc
  rename H ch_total
  rename I ch_water_tap
  rename J ch_water_handpump
  rename K ch_water_tubewell
  rename L ch_water_well
  rename M ch_water_tankpondlake
  rename N ch_water_rivercanal
  rename O ch_water_spring
  rename P ch_water_other
  destring ch_total - ch_water_other, replace
  local state = st_code
  save "$hpca01/H8/`state'_H8.dta", replace
}
local statafiles: dir "$hpca01/H8/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_water_loc="TOT" if ch_water_loc=="Total"
replace ch_water_loc="WP" if ch_water_loc=="Within Premises"
replace ch_water_loc="NP" if ch_water_loc=="Near Premises"
replace ch_water_loc="AW" if ch_water_loc=="Away"
drop if inlist(ch_water_loc,"TOT","WP","NP","AW")==0 
reshape wide ch_total - ch_water_other, i(st_code-rural_urban) j(ch_water_loc) string

gen HPCA_COUNT_H8 = ch_totalTOT
gen HPCA_dw_tfts_tfus = ch_water_tapTOT/ch_totalTOT
gen HPCA_dw_cw_uw = ch_water_wellTOT/ch_totalTOT
gen HPCA_dw_hp = ch_water_handpumpTOT/ch_totalTOT
gen HPCA_dw_tb = ch_water_tubewellTOT/ch_totalTOT
gen HPCA_dw_sp = ch_water_springTOT/ch_totalTOT
gen HPCA_dw_rc = ch_water_rivercanalTOT/ch_totalTOT
gen HPCA_dw_tpl = ch_water_tankpondlakeTOT/ch_totalTOT
gen HPCA_dw_os = ch_water_otherTOT/ch_totalTOT
gen HPCA_w_within = ch_totalWP/ch_totalTOT 
gen HPCA_w_near = ch_totalNP/ch_totalTOT
gen HPCA_w_away = ch_totalAW/ch_totalTOT

save "$hpca01/hpca01_stata/H8_all.dta", replace
}

{
cd "$hpca01/H8ST"
local files: dir "$hpca01/H8ST/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=5
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_water_loc_st
  rename H ch_total_st
  rename I ch_water_tap_st
  rename J ch_water_handpump_st
  rename K ch_water_tubewell_st
  rename L ch_water_well_st
  rename M ch_water_tankpondlake_st
  rename N ch_water_rivercanal_st
  rename O ch_water_spring_st
  rename P ch_water_other_st
  destring ch_total - ch_water_other, replace
  local state = st_code
  save "$hpca01/H8ST/`state'_H8ST.dta", replace
}
local statafiles: dir "$hpca01/H8ST/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_water_loc_st="TOT" if ch_water_loc_st=="Total"
replace ch_water_loc_st="WP" if ch_water_loc_st=="Within Premises"
replace ch_water_loc_st="NP" if ch_water_loc_st=="Near Premises"
replace ch_water_loc_st="AW" if ch_water_loc_st=="Away"
drop if inlist(ch_water_loc_st,"TOT","WP","NP","AW")==0 
reshape wide ch_total - ch_water_other, i(st_code-rural_urban) j(ch_water_loc_st) string

gen HPCA_ST_COUNT_H8 = ch_total_stTOT
gen HPCA_ST_dw_tfts_tfus = ch_water_tap_stTOT/ch_total_stTOT
gen HPCA_ST_dw_cw_uw = ch_water_well_stTOT/ch_total_stTOT
gen HPCA_ST_dw_hp = ch_water_handpump_stTOT/ch_total_stTOT
gen HPCA_ST_dw_tb = ch_water_tubewell_stTOT/ch_total_stTOT
gen HPCA_ST_dw_sp = ch_water_spring_stTOT/ch_total_stTOT
gen HPCA_ST_dw_rc = ch_water_rivercanal_stTOT/ch_total_stTOT
gen HPCA_ST_dw_tpl = ch_water_tankpondlake_stTOT/ch_total_stTOT
gen HPCA_ST_dw_os = ch_water_other_stTOT/ch_total_stTOT
gen HPCA_ST_w_within = ch_total_stWP/ch_total_stTOT 
gen HPCA_ST_w_near = ch_total_stNP/ch_total_stTOT
gen HPCA_ST_w_away = ch_total_stAW/ch_total_stTOT

save "$hpca01/hpca01_stata/H8ST_all.dta", replace
}


{
cd "$hpca01/H8SC"
local files: dir "$hpca01/H8SC/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=5
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_water_loc_sc
  rename H ch_total_sc
  rename I ch_water_tap_sc
  rename J ch_water_handpump_sc
  rename K ch_water_tubewell_sc
  rename L ch_water_well_sc
  rename M ch_water_tankpondlake_sc
  rename N ch_water_rivercanal_sc
  rename O ch_water_spring_sc
  rename P ch_water_other_sc
  destring ch_total - ch_water_other, replace
  local state = st_code
  save "$hpca01/H8SC/`state'_H8SC.dta", replace
}
local statafiles: dir "$hpca01/H8SC/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_water_loc_sc="TOT" if ch_water_loc_sc=="Total"
replace ch_water_loc_sc="WP" if ch_water_loc_sc=="Within Premises"
replace ch_water_loc_sc="NP" if ch_water_loc_sc=="Near Premises"
replace ch_water_loc_sc="AW" if ch_water_loc_sc=="Away"
drop if inlist(ch_water_loc_sc,"TOT","WP","NP","AW")==0 
reshape wide ch_total - ch_water_other, i(st_code-rural_urban) j(ch_water_loc_sc) string

gen HPCA_SC_COUNT_H8 = ch_total_scTOT
gen HPCA_SC_dw_tfts_tfus = ch_water_tap_scTOT/ch_total_scTOT
gen HPCA_SC_dw_cw_uw = ch_water_well_scTOT/ch_total_scTOT
gen HPCA_SC_dw_hp = ch_water_handpump_scTOT/ch_total_scTOT
gen HPCA_SC_dw_tb = ch_water_tubewell_scTOT/ch_total_scTOT
gen HPCA_SC_dw_sp = ch_water_spring_scTOT/ch_total_scTOT
gen HPCA_SC_dw_rc = ch_water_rivercanal_scTOT/ch_total_scTOT
gen HPCA_SC_dw_tpl = ch_water_tankpondlake_scTOT/ch_total_scTOT
gen HPCA_SC_dw_os = ch_water_other_scTOT/ch_total_scTOT
gen HPCA_SC_w_within = ch_total_scWP/ch_total_scTOT 
gen HPCA_SC_w_near = ch_total_scNP/ch_total_scTOT
gen HPCA_SC_w_away = ch_total_scAW/ch_total_scTOT

save "$hpca01/hpca01_stata/H8SC_all.dta", replace
}


{
cd "$hpca01/H9"
local files: dir "$hpca01/H9/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=5
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total
  rename H ch_light_electric
  rename I ch_light_kerosene
  rename J ch_light_solar
  rename K ch_light_oil
  rename L ch_light_other
  rename M ch_light_none
  destring ch_total - ch_light_none, replace
  local state = st_code
  save "$hpca01/H9/`state'_H9.dta", replace
}
local statafiles: dir "$hpca01/H9/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_COUNT_H9 = ch_total
gen HPCA_msl_elec = ch_light_electric/ch_total
gen HPCA_msl_ker = ch_light_kerosene/ch_total
gen HPCA_msl_sol = ch_light_solar/ch_total
gen HPCA_msl_oo = ch_light_oil/ch_total
gen HPCA_msl_ao = ch_light_other/ch_total
gen HPCA_msl_nl = ch_light_none/ch_total

save "$hpca01/hpca01_stata/H9_all.dta", replace
}

{
cd "$hpca01/H9ST"
local files: dir "$hpca01/H9ST/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=5
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total_st
  rename H ch_light_electric_st
  rename I ch_light_kerosene_st
  rename J ch_light_solar_st
  rename K ch_light_oil_st
  rename L ch_light_other_st
  rename M ch_light_none_st
  destring ch_total - ch_light_none, replace
  local state = st_code
  save "$hpca01/H9ST/`state'_H9ST.dta", replace
}
local statafiles: dir "$hpca01/H9ST/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_ST_COUNT_H9 = ch_total
gen HPCA_ST_msl_elec = ch_light_electric/ch_total
gen HPCA_ST_msl_ker = ch_light_kerosene/ch_total
gen HPCA_ST_msl_sol = ch_light_solar/ch_total
gen HPCA_ST_msl_oo = ch_light_oil/ch_total
gen HPCA_ST_msl_ao = ch_light_other/ch_total
gen HPCA_ST_msl_nl = ch_light_none/ch_total

save "$hpca01/hpca01_stata/H9ST_all.dta", replace
}


{
cd "$hpca01/H9SC"
local files: dir "$hpca01/H9SC/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=5
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total_sc
  rename H ch_light_electric_sc
  rename I ch_light_kerosene_sc
  rename J ch_light_solar_sc
  rename K ch_light_oil_sc
  rename L ch_light_other_sc
  rename M ch_light_none_sc
  destring ch_total - ch_light_none, replace
  local state = st_code
  save "$hpca01/H9SC/`state'_H9SC.dta", replace
}
local statafiles: dir "$hpca01/H9SC/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_SC_COUNT_H9 = ch_total
gen HPCA_SC_msl_elec = ch_light_electric/ch_total
gen HPCA_SC_msl_ker = ch_light_kerosene/ch_total
gen HPCA_SC_msl_sol = ch_light_solar/ch_total
gen HPCA_SC_msl_oo = ch_light_oil/ch_total
gen HPCA_SC_msl_ao = ch_light_other/ch_total
gen HPCA_SC_msl_nl = ch_light_none/ch_total

save "$hpca01/hpca01_stata/H9SC_all.dta", replace
}

{
cd "$hpca01/H10"
local files: dir "$hpca01/H10/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=7
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total
  rename H ch_bathroom_inhouse
  rename I ch_latrine_pit
  rename J ch_latrine_wc
  rename K ch_latrine_other
  rename L ch_latrine_none
  rename M ch_wastewater_closeddrain
  rename N ch_wastewater_opendrain
  rename O ch_wastewater_nodrain

  destring ch_total - ch_wastewater_nodrain, replace
  local state = st_code
  save "$hpca01/H10/`state'_H10.dta", replace
}
local statafiles: dir "$hpca01/H10/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_COUNT_H10 = ch_total
gen HPCA_lat_premise = (ch_latrine_pit + ch_latrine_wc + ch_latrine_other)/ch_total
gen HPCA_lat_pit_svi_sop = ch_latrine_pit/ch_total
gen HPCA_no_lat = ch_latrine_none/ch_total
gen HPCA_bath_bath_ewr = ch_bathroom_inhouse/ch_total
gen HPCA_bath_no = 1-ch_bathroom_inhouse/ch_total
gen HPCA_ww_cd = ch_wastewater_closeddrain/ch_total
gen HPCA_ww_od = ch_wastewater_opendrain/ch_total
gen HPCA_ww_nd = ch_wastewater_nodrain/ch_total

save "$hpca01/hpca01_stata/H10_all.dta", replace
}


{
cd "$hpca01/H10ST"
local files: dir "$hpca01/H10ST/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=7
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total_st
  rename H ch_bathroom_inhouse_st
  rename I ch_latrine_pit_st
  rename J ch_latrine_wc_st
  rename K ch_latrine_other_st
  rename L ch_latrine_none_st
  rename M ch_wastewater_closeddrain_st
  rename N ch_wastewater_opendrain_st
  rename O ch_wastewater_nodrain_st

  destring ch_total - ch_wastewater_nodrain, replace
  local state = st_code
  save "$hpca01/H10ST/`state'_H10ST.dta", replace
}
local statafiles: dir "$hpca01/H10ST/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_ST_COUNT_H10 = ch_total
gen HPCA_ST_lat_premise = (ch_latrine_pit + ch_latrine_wc + ch_latrine_other)/ch_total
gen HPCA_ST_lat_pit_svi_sop = ch_latrine_pit/ch_total
gen HPCA_ST_no_lat = ch_latrine_none/ch_total
gen HPCA_ST_bath_bath_ewr = ch_bathroom_inhouse/ch_total
gen HPCA_ST_bath_no = 1 - ch_bathroom_inhouse/ch_total
gen HPCA_ST_ww_cd = ch_wastewater_closeddrain/ch_total
gen HPCA_ST_ww_od = ch_wastewater_opendrain/ch_total
gen HPCA_ST_ww_nd = ch_wastewater_nodrain/ch_total

save "$hpca01/hpca01_stata/H10ST_all.dta", replace
}

{
cd "$hpca01/H10SC"
local files: dir "$hpca01/H10SC/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=7
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total_sc
  rename H ch_bathroom_inhouse_sc
  rename I ch_latrine_pit_sc
  rename J ch_latrine_wc_sc
  rename K ch_latrine_other_sc
  rename L ch_latrine_none_sc
  rename M ch_wastewater_closeddrain_sc
  rename N ch_wastewater_opendrain_sc
  rename O ch_wastewater_nodrain_sc

  destring ch_total - ch_wastewater_nodrain, replace
  local state = st_code
  save "$hpca01/H10SC/`state'_H10SC.dta", replace
}
local statafiles: dir "$hpca01/H10SC/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_SC_COUNT_H10 = ch_total
gen HPCA_SC_lat_premise = (ch_latrine_pit + ch_latrine_wc + ch_latrine_other)/ch_total
gen HPCA_SC_lat_pit_svi_sop = ch_latrine_pit/ch_total
gen HPCA_SC_no_lat = ch_latrine_none/ch_total
gen HPCA_SC_bath_bath_ewr = ch_bathroom_inhouse/ch_total
gen HPCA_SC_bath_no = 1 - ch_bathroom_inhouse/ch_total
gen HPCA_SC_ww_cd = ch_wastewater_closeddrain/ch_total
gen HPCA_SC_ww_od = ch_wastewater_opendrain/ch_total
gen HPCA_SC_ww_nd = ch_wastewater_nodrain/ch_total

save "$hpca01/hpca01_stata/H10SC_all.dta", replace
}

{
cd "$hpca01/H11"
local files: dir "$hpca01/H11/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=5
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_sep_kitchen
  rename H ch_households
  rename I ch_cooking_wood
  rename J ch_cooking_cropresidue
  rename K ch_cooking_cowdung
  rename L ch_cooking_coal
  rename M ch_cooking_kerosene
  rename N ch_cooking_lpg
  rename O ch_cooking_electric
  rename P ch_cooking_biogas
  rename Q ch_cooking_other
  rename R ch_cooking_none

  destring ch_households - ch_cooking_none, replace
  local state = st_code
  save "$hpca01/H11/`state'_H11.dta", replace
}
local statafiles: dir "$hpca01/H11/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_sep_kitchen="TOT" if ch_sep_kitchen=="Total"
replace ch_sep_kitchen="A"  if ch_sep_kitchen=="Available"
replace ch_sep_kitchen="NA" if ch_sep_kitchen=="Not available"
replace ch_sep_kitchen="OP" if ch_sep_kitchen=="Cooking in Open"
replace ch_sep_kitchen="NC" if ch_sep_kitchen=="No Cooking"
drop if ch_sep_kitchen==""
reshape wide ch_households-ch_cooking_none, i(st_code-rural_urban) j(ch_sep_kitchen) string

gen HPCA_COUNT_H11 = ch_householdsTOT
gen HPCA_cook_fw = ch_cooking_woodTOT/ch_householdsTOT
gen HPCA_cook_cr = ch_cooking_cropresidueTOT/ch_householdsTOT
gen HPCA_cook_cc = ch_cooking_cowdungTOT/ch_householdsTOT
gen HPCA_cook_clc = ch_cooking_coalTOT/ch_householdsTOT
gen HPCA_cook_ker = ch_cooking_keroseneTOT/ch_householdsTOT
gen HPCA_cook_lpg_png = ch_cooking_lpgTOT/ch_householdsTOT
gen HPCA_cook_elec = ch_cooking_electricTOT/ch_householdsTOT
gen HPCA_cook_biog = ch_cooking_biogasTOT/ch_householdsTOT
gen HPCA_cook_ao = ch_cooking_otherTOT/ch_householdsTOT
gen HPCA_cook_no = ch_cooking_noneTOT/ch_householdsTOT
gen HPCA_cook_ih = (ch_householdsA+ch_householdsNA)/ch_householdsTOT
gen HPCA_cook_ih_kitchen = ch_householdsA/ch_householdsTOT
gen HPCA_cook_ih_no_kitchen = ch_householdsNA/ch_householdsTOT
gen HPCA_cook_oh = ch_householdsOP/ch_householdsTOT
gen HPCA_cook_no2 = ch_householdsNC/ch_householdsTOT

save "$hpca01/hpca01_stata/H11_all.dta", replace
}

{
cd "$hpca01/H11ST"
local files: dir "$hpca01/H11ST/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=5
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_sep_kitchen_st
  rename H ch_households_st
  rename I ch_cooking_wood_st
  rename J ch_cooking_cropresidue_st
  rename K ch_cooking_cowdung_st
  rename L ch_cooking_coal_st
  rename M ch_cooking_kerosene_st
  rename N ch_cooking_lpg_st
  rename O ch_cooking_electric_st
  rename P ch_cooking_biogas_st
  rename Q ch_cooking_other_st
  rename R ch_cooking_none_st

  destring ch_households - ch_cooking_none, replace
  local state = st_code
  save "$hpca01/H11ST/`state'_H11ST.dta", replace
}
local statafiles: dir "$hpca01/H11ST/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_sep_kitchen_st="TOT" if ch_sep_kitchen_st=="Total"
replace ch_sep_kitchen_st="A"  if ch_sep_kitchen_st=="Available"
replace ch_sep_kitchen_st="NA" if ch_sep_kitchen_st=="Not available"
replace ch_sep_kitchen_st="OP" if ch_sep_kitchen_st=="Cooking in Open"
replace ch_sep_kitchen_st="NC" if ch_sep_kitchen_st=="No Cooking"
drop if inlist(ch_sep_kitchen_st,"TOT","A","NA","OP","NC")==0
reshape wide ch_households_st-ch_cooking_none_st, i(st_code-rural_urban) j(ch_sep_kitchen_st) string

gen HPCA_ST_COUNT_H11 = ch_households_stTOT
gen HPCA_ST_cook_fw = ch_cooking_wood_stTOT/ch_households_stTOT
gen HPCA_ST_cook_cr = ch_cooking_cropresidue_stTOT/ch_households_stTOT
gen HPCA_ST_cook_cc = ch_cooking_cowdung_stTOT/ch_households_stTOT
gen HPCA_ST_cook_clc = ch_cooking_coal_stTOT/ch_households_stTOT
gen HPCA_ST_cook_ker = ch_cooking_kerosene_stTOT/ch_households_stTOT
gen HPCA_ST_cook_lpg_png = ch_cooking_lpg_stTOT/ch_households_stTOT
gen HPCA_ST_cook_elec = ch_cooking_electric_stTOT/ch_households_stTOT
gen HPCA_ST_cook_biog = ch_cooking_biogas_stTOT/ch_households_stTOT
gen HPCA_ST_cook_ao = ch_cooking_other_stTOT/ch_households_stTOT
gen HPCA_ST_cook_no = ch_cooking_none_stTOT/ch_households_stTOT
gen HPCA_ST_cook_ih = (ch_households_stA+ch_households_stNA)/ch_households_stTOT
gen HPCA_ST_cook_ih_kitchen = ch_households_stA/ch_households_stTOT
gen HPCA_ST_cook_ih_no_kitchen = ch_households_stNA/ch_households_stTOT
gen HPCA_ST_cook_oh = ch_households_stOP/ch_households_stTOT
gen HPCA_ST_cook_no2 = ch_households_stNC/ch_households_stTOT

save "$hpca01/hpca01_stata/H11ST_all.dta", replace
}


{
cd "$hpca01/H11SC"
local files: dir "$hpca01/H11SC/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=5
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_sep_kitchen_sc
  rename H ch_households_sc
  rename I ch_cooking_wood_sc
  rename J ch_cooking_cropresidue_sc
  rename K ch_cooking_cowdung_sc
  rename L ch_cooking_coal_sc
  rename M ch_cooking_kerosene_sc
  rename N ch_cooking_lpg_sc
  rename O ch_cooking_electric_sc
  rename P ch_cooking_biogas_sc
  rename Q ch_cooking_other_sc
  rename R ch_cooking_none_sc

  destring ch_households - ch_cooking_none, replace
  local state = st_code
  save "$hpca01/H11SC/`state'_H11SC.dta", replace
}
local statafiles: dir "$hpca01/H11SC/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_sep_kitchen_sc="TOT" if ch_sep_kitchen_sc=="Total"
replace ch_sep_kitchen_sc="A"  if ch_sep_kitchen_sc=="Available"
replace ch_sep_kitchen_sc="NA" if ch_sep_kitchen_sc=="Not available"
replace ch_sep_kitchen_sc="OP" if ch_sep_kitchen_sc=="Cooking in Open"
replace ch_sep_kitchen_sc="NC" if ch_sep_kitchen_sc=="No Cooking"
drop if inlist(ch_sep_kitchen_sc,"TOT","A","NA","OP","NC")==0
reshape wide ch_households_sc-ch_cooking_none_sc, i(st_code-rural_urban) j(ch_sep_kitchen_sc) string

gen HPCA_SC_COUNT_H11 = ch_households_scTOT
gen HPCA_SC_cook_fw = ch_cooking_wood_scTOT/ch_households_scTOT
gen HPCA_SC_cook_cr = ch_cooking_cropresidue_scTOT/ch_households_scTOT
gen HPCA_SC_cook_cc = ch_cooking_cowdung_scTOT/ch_households_scTOT
gen HPCA_SC_cook_clc = ch_cooking_coal_scTOT/ch_households_scTOT
gen HPCA_SC_cook_ker = ch_cooking_kerosene_scTOT/ch_households_scTOT
gen HPCA_SC_cook_lpg_png = ch_cooking_lpg_scTOT/ch_households_scTOT
gen HPCA_SC_cook_elec = ch_cooking_electric_scTOT/ch_households_scTOT
gen HPCA_SC_cook_biog = ch_cooking_biogas_scTOT/ch_households_scTOT
gen HPCA_SC_cook_ao = ch_cooking_other_scTOT/ch_households_scTOT
gen HPCA_SC_cook_no = ch_cooking_none_scTOT/ch_households_scTOT
gen HPCA_SC_cook_ih = (ch_households_scA+ch_households_scNA)/ch_households_scTOT
gen HPCA_SC_cook_ih_kitchen = ch_households_scA/ch_households_scTOT
gen HPCA_SC_cook_ih_no_kitchen = ch_households_scNA/ch_households_scTOT
gen HPCA_SC_cook_oh = ch_households_scOP/ch_households_scTOT
gen HPCA_SC_cook_no2 = ch_households_scNC/ch_households_scTOT

save "$hpca01/hpca01_stata/H11SC_all.dta", replace
}

{
cd "$hpca01/H12"
local files: dir "$hpca01/H12/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_water_source
  rename H ch_water_loc
  rename I ch_total
  rename J ch_elec_avail
  rename K ch_elec_unavail
  rename L ch_latrine_avail
  rename M ch_latrine_unavail

  destring ch_total - ch_latrine_unavail, replace
  local state = st_code
  save "$hpca01/H12/`state'_H12.dta", replace
}
local statafiles: dir "$hpca01/H12/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_water_source="TOT" if ch_water_source=="All Sources"
replace ch_water_source="T" if ch_water_source=="Tap"
replace ch_water_source="HP" if ch_water_source=="Hand pump"
replace ch_water_source="TW" if ch_water_source=="Tube well"
replace ch_water_source="W" if ch_water_source=="Well"
replace ch_water_source="OT" if ch_water_source=="All others"
replace ch_water_loc="TOT" if ch_water_loc=="Total"
replace ch_water_loc="WP" if ch_water_loc=="Within Premises"
replace ch_water_loc="NP" if ch_water_loc=="Near Premises"
replace ch_water_loc="AW" if ch_water_loc=="Away"
gen j = ch_water_source + "_" + ch_water_loc
drop if inlist(ch_water_source,"TOT","T","HP","TW","W","OT")==0 | inlist(ch_water_loc,"TOT","WP","NP","AW")==0 
drop ch_water_source ch_water_loc
reshape wide ch_total-ch_latrine_unavail, i(st_code-rural_urban) j(j) string
save "$hpca01/hpca01_stata/H12_all.dta", replace
}

{
cd "$hpca01/H12ST"
local files: dir "$hpca01/H12ST/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_water_source_st
  rename H ch_water_loc_st
  rename I ch_total_st
  rename J ch_elec_avail_st
  rename K ch_elec_unavail_st
  rename L ch_latrine_avail_st
  rename M ch_latrine_unavail_st

  destring ch_total - ch_latrine_unavail, replace
  local state = st_code
  save "$hpca01/H12ST/`state'_H12ST.dta", replace
}
local statafiles: dir "$hpca01/H12ST/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_water_source_st="TOT" if ch_water_source_st=="All Sources"
replace ch_water_source_st="T" if ch_water_source_st=="Tap"
replace ch_water_source_st="HP" if ch_water_source_st=="Hand pump"
replace ch_water_source_st="TW" if ch_water_source_st=="Tube well"
replace ch_water_source_st="W" if ch_water_source_st=="Well"
replace ch_water_source_st="OT" if ch_water_source_st=="All others"
replace ch_water_loc_st="TOT" if ch_water_loc_st=="Total"
replace ch_water_loc_st="WP" if ch_water_loc_st=="Within Premises"
replace ch_water_loc_st="NP" if ch_water_loc_st=="Near Premises"
replace ch_water_loc_st="AW" if ch_water_loc_st=="Away"
gen j = ch_water_source_st + "_" + ch_water_loc_st
drop if inlist(ch_water_source_st,"TOT","T","HP","TW","W","OT")==0 | inlist(ch_water_loc_st,"TOT","WP","NP","AW")==0 
drop ch_water_source_st ch_water_loc_st
reshape wide ch_total_st-ch_latrine_unavail_st, i(st_code-rural_urban) j(j) string
save "$hpca01/hpca01_stata/H12ST_all.dta", replace
}

{
cd "$hpca01/H12SC"
local files: dir "$hpca01/H12SC/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_water_source_sc
  rename H ch_water_loc_sc
  rename I ch_total_sc
  rename J ch_elec_avail_sc
  rename K ch_elec_unavail_sc
  rename L ch_latrine_avail_sc
  rename M ch_latrine_unavail_sc

  destring ch_total - ch_latrine_unavail, replace
  local state = st_code
  save "$hpca01/H12SC/`state'_H12SC.dta", replace
}
local statafiles: dir "$hpca01/H12SC/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_water_source_sc="TOT" if ch_water_source_sc=="All Sources"
replace ch_water_source_sc="T" if ch_water_source_sc=="Tap"
replace ch_water_source_sc="HP" if ch_water_source_sc=="Hand pump"
replace ch_water_source_sc="TW" if ch_water_source_sc=="Tube well"
replace ch_water_source_sc="W" if ch_water_source_sc=="Well"
replace ch_water_source_sc="OT" if ch_water_source_sc=="All others"
replace ch_water_loc_sc="TOT" if ch_water_loc_sc=="Total"
replace ch_water_loc_sc="WP" if ch_water_loc_sc=="Within Premises"
replace ch_water_loc_sc="NP" if ch_water_loc_sc=="Near Premises"
replace ch_water_loc_sc="AW" if ch_water_loc_sc=="Away"
gen j = ch_water_source_sc + "_" + ch_water_loc_sc
drop if inlist(ch_water_source_sc,"TOT","T","HP","TW","W","OT")==0 | inlist(ch_water_loc_sc,"TOT","WP","NP","AW")==0 
drop ch_water_source_sc ch_water_loc_sc
reshape wide ch_total_sc-ch_latrine_unavail_sc, i(st_code-rural_urban) j(j) string
save "$hpca01/hpca01_stata/H12SC_all.dta", replace
}

{
cd "$hpca01/H12A"
local files: dir "$hpca01/H12A/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_water_source_a
  rename H ch_water_loc_a
  rename I ch_total_a
  rename J ch_elec_avail_a
  rename K ch_elec_unavail_a
  rename L ch_latrine_avail_a
  rename M ch_latrine_unavail_a

  destring ch_total - ch_latrine_unavail, replace
  local state = st_code
  save "$hpca01/H12A/`state'_H12A.dta", replace
}
local statafiles: dir "$hpca01/H12A/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_water_source_a="TOT" if ch_water_source_a=="All Sources"
replace ch_water_source_a="T" if ch_water_source_a=="Tap"
replace ch_water_source_a="HP" if ch_water_source_a=="Hand pump"
replace ch_water_source_a="TW" if ch_water_source_a=="Tube well"
replace ch_water_source_a="W" if ch_water_source_a=="Well"
replace ch_water_source_a="OT" if ch_water_source_a=="All others"
replace ch_water_loc_a="TOT" if ch_water_loc_a=="Total"
replace ch_water_loc_a="WP" if ch_water_loc_a=="Within Premises"
replace ch_water_loc_a="NP" if ch_water_loc_a=="Near Premises"
replace ch_water_loc_a="AW" if ch_water_loc_a=="Away"
gen j = ch_water_source_a + "_" + ch_water_loc_a
drop if inlist(ch_water_source_a,"TOT","T","HP","TW","W","OT")==0 | inlist(ch_water_loc_a,"TOT","WP","NP","AW")==0 
drop ch_water_source_a ch_water_loc_a
reshape wide ch_total_a-ch_latrine_unavail_a, i(st_code-rural_urban) j(j) string
save "$hpca01/hpca01_stata/H12A_all.dta", replace
}

{
cd "$hpca01/H12ASC"
local files: dir "$hpca01/H12ASC/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_water_source_sca
  rename H ch_water_loc_sca
  rename I ch_total_sca
  rename J ch_elec_avail_sca
  rename K ch_elec_unavail_sca
  rename L ch_latrine_avail_sca
  rename M ch_latrine_unavail_sca

  destring ch_total - ch_latrine_unavail, replace
  local state = st_code
  save "$hpca01/H12ASC/`state'_H12ASC.dta", replace
}
local statafiles: dir "$hpca01/H12ASC/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_water_source_sca="TOT" if ch_water_source_sca=="All Sources"
replace ch_water_source_sca="T" if ch_water_source_sca=="Tap"
replace ch_water_source_sca="HP" if ch_water_source_sca=="Hand pump"
replace ch_water_source_sca="TW" if ch_water_source_sca=="Tube well"
replace ch_water_source_sca="W" if ch_water_source_sca=="Well"
replace ch_water_source_sca="OT" if ch_water_source_sca=="All others"
replace ch_water_loc_sca="TOT" if ch_water_loc_sca=="Total"
replace ch_water_loc_sca="WP" if ch_water_loc_sca=="Within Premises"
replace ch_water_loc_sca="NP" if ch_water_loc_sca=="Near Premises"
replace ch_water_loc_sca="AW" if ch_water_loc_sca=="Away"
gen j = ch_water_source_sca + "_" + ch_water_loc_sca
drop if inlist(ch_water_source_sca,"TOT","T","HP","TW","W","OT")==0 | inlist(ch_water_loc_sca,"TOT","WP","NP","AW")==0 
drop ch_water_source_sca ch_water_loc_sca
reshape wide ch_total_sca-ch_latrine_unavail_sca, i(st_code-rural_urban) j(j) string
save "$hpca01/hpca01_stata/H12ASC_all.dta", replace
}

{
cd "$hpca01/H12AST"
local files: dir "$hpca01/H12AST/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_water_source_sta
  rename H ch_water_loc_sta
  rename I ch_total_sta
  rename J ch_elec_avail_sta
  rename K ch_elec_unavail_sta
  rename L ch_latrine_avail_sta
  rename M ch_latrine_unavail_sta

  destring ch_total - ch_latrine_unavail, replace
  local state = st_code
  save "$hpca01/H12AST/`state'_H12AST.dta", replace
}
local statafiles: dir "$hpca01/H12AST/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_water_source_sta="TOT" if ch_water_source_sta=="All Sources"
replace ch_water_source_sta="T" if ch_water_source_sta=="Tap"
replace ch_water_source_sta="HP" if ch_water_source_sta=="Hand pump"
replace ch_water_source_sta="TW" if ch_water_source_sta=="Tube well"
replace ch_water_source_sta="W" if ch_water_source_sta=="Well"
replace ch_water_source_sta="OT" if ch_water_source_sta=="All others"
replace ch_water_loc_sta="TOT" if ch_water_loc_sta=="Total"
replace ch_water_loc_sta="WP" if ch_water_loc_sta=="Within Premises"
replace ch_water_loc_sta="NP" if ch_water_loc_sta=="Near Premises"
replace ch_water_loc_sta="AW" if ch_water_loc_sta=="Away"
gen j = ch_water_source_sta + "_" + ch_water_loc_sta
drop if inlist(ch_water_source_sta,"TOT","T","HP","TW","W","OT")==0 | inlist(ch_water_loc_sta,"TOT","WP","NP","AW")==0 
drop ch_water_source_sta ch_water_loc_sta
reshape wide ch_total_sta-ch_latrine_unavail_sta, i(st_code-rural_urban) j(j) string
save "$hpca01/hpca01_stata/H12AST_all.dta", replace
}

{
cd "$hpca01/H12B"
local files: dir "$hpca01/H12B/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=7
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_ownership_b
  rename H ch_total_b
  rename I ch_totpop_b
  rename J ch_elec_avail_nr_b
  rename K ch_elec_avail_per1k_b
  rename L ch_toilet_avail_nr_b
  rename M ch_toilet_avail_per1k_b

  destring ch_total - ch_toilet_avail_per1k, replace
  local state = st_code
  save "$hpca01/H12B/`state'_H12B.dta", replace
}
local statafiles: dir "$hpca01/H12B/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_ownership_b="TOT" if ch_ownership_b=="Total"
replace ch_ownership_b="RENT" if ch_ownership_b=="Rented"
replace ch_ownership_b="OWN" if ch_ownership_b=="Owned"
replace ch_ownership_b="OT" if ch_ownership_b=="Any other"
drop if inlist(ch_ownership_b,"TOT","RENT","OWN","OT")==0 
reshape wide ch_total_b-ch_toilet_avail_per1k_b, i(st_code-rural_urban) j(ch_ownership_b) string
save "$hpca01/hpca01_stata/H12B_all.dta", replace
}

{
cd "$hpca01/H12BST"
local files: dir "$hpca01/H12BST/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=7
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_ownership_bst
  rename H ch_total_bst
  rename I ch_totpop_bst
  rename J ch_elec_avail_nr_bst
  rename K ch_elec_avail_per1k_bst
  rename L ch_toilet_avail_nr_bst
  rename M ch_toilet_avail_per1k_bst

  destring ch_total - ch_toilet_avail_per1k, replace
  local state = st_code
  save "$hpca01/H12BST/`state'_H12BST.dta", replace
}
local statafiles: dir "$hpca01/H12BST/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_ownership_bst="TOT" if ch_ownership_bst=="Total"
replace ch_ownership_bst="RENT" if ch_ownership_bst=="Rented"
replace ch_ownership_bst="OWN" if ch_ownership_bst=="Owned"
replace ch_ownership_bst="OT" if ch_ownership_bst=="Any other"
drop if inlist(ch_ownership_bst,"TOT","RENT","OWN","OT")==0 
reshape wide ch_total_bst-ch_toilet_avail_per1k_bst, i(st_code-rural_urban) j(ch_ownership_bst) string
save "$hpca01/hpca01_stata/H12BST_all.dta", replace
}

{
cd "$hpca01/H12BSC"
local files: dir "$hpca01/H12BSC/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=7
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_ownership_bsc
  rename H ch_total_bsc
  rename I ch_totpop_bsc
  rename J ch_elec_avail_nr_bsc
  rename K ch_elec_avail_per1k_bsc
  rename L ch_toilet_avail_nr_bsc
  rename M ch_toilet_avail_per1k_bsc

  destring ch_total - ch_toilet_avail_per1k, replace
  local state = st_code
  save "$hpca01/H12BSC/`state'_H12BSC.dta", replace
}
local statafiles: dir "$hpca01/H12BSC/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop
replace ch_ownership_bsc="TOT" if ch_ownership_bsc=="Total"
replace ch_ownership_bsc="RENT" if ch_ownership_bsc=="Rented"
replace ch_ownership_bsc="OWN" if ch_ownership_bsc=="Owned"
replace ch_ownership_bsc="OT" if ch_ownership_bsc=="Any other"
drop if inlist(ch_ownership_bsc,"TOT","RENT","OWN","OT")==0 
reshape wide ch_total_bsc-ch_toilet_avail_per1k_bsc, i(st_code-rural_urban) j(ch_ownership_bsc) string
save "$hpca01/hpca01_stata/H12BSC_all.dta", replace
}

{
cd "$hpca01/H13"
local files: dir "$hpca01/H13/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total
  rename H ch_using_banking
  rename I ch_radio
  rename J ch_tv
  rename K ch_phone
  rename L ch_bike
  rename M ch_motorcycle
  rename N ch_car
  rename O ch_none

  destring ch_total - ch_none, replace
  local state = st_code
  save "$hpca01/H13/`state'_H13.dta", replace
}
local statafiles: dir "$hpca01/H13/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_COUNT_H13 = ch_total
gen HPCA_bank = ch_using_banking/ch_total
gen HPCA_assets_rt = ch_radio/ch_total
gen HPCA_assets_tv = ch_tv/ch_total
gen HPCA_assets_tel_l_m_b = ch_phone/ch_total
gen HPCA_assets_bic = ch_bike/ch_total
gen HPCA_assets_smm = ch_motorcycle/ch_total
gen HPCA_assets_cjv = ch_car/ch_total
gen HPCA_assets_none = ch_none/ch_total

save "$hpca01/hpca01_stata/H13_all.dta", replace
}

{
cd "$hpca01/H13ST"
local files: dir "$hpca01/H13ST/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total_st
  rename H ch_using_banking_st
  rename I ch_radio_st
  rename J ch_tv_st
  rename K ch_phone_st
  rename L ch_bike_st
  rename M ch_motorcycle_st
  rename N ch_car_st
  rename O ch_none_st

  destring ch_total - ch_none, replace
  local state = st_code
  save "$hpca01/H13ST/`state'_H13ST.dta", replace
}
local statafiles: dir "$hpca01/H13ST/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_ST_COUNT_H13 = ch_total
gen HPCA_ST_bank = ch_using_banking/ch_total
gen HPCA_ST_assets_rt = ch_radio/ch_total
gen HPCA_ST_assets_tv = ch_tv/ch_total
gen HPCA_ST_assets_tel_l_m_b = ch_phone/ch_total
gen HPCA_ST_assets_bic = ch_bike/ch_total
gen HPCA_ST_assets_smm = ch_motorcycle/ch_total
gen HPCA_ST_assets_cjv = ch_car/ch_total
gen HPCA_ST_assets_none = ch_none/ch_total

save "$hpca01/hpca01_stata/H13ST_all.dta", replace
}

{
cd "$hpca01/H13SC"
local files: dir "$hpca01/H13SC/" files "*.xls"
foreach file in `files' {
  import excel using "`file'", clear
  gen counter = _n
  drop if counter <=6
  drop counter A
  rename B st_code
  rename C dt_code
  rename D tahsil_code
  rename E name
  rename F rural_urban
  rename G ch_total_sc
  rename H ch_using_banking_sc
  rename I ch_radio_sc
  rename J ch_tv_sc
  rename K ch_phone_sc
  rename L ch_bike_sc
  rename M ch_motorcycle_sc
  rename N ch_car_sc
  rename O ch_none_sc

  destring ch_total - ch_none, replace
  local state = st_code
  save "$hpca01/H13SC/`state'_H13SC.dta", replace
}
local statafiles: dir "$hpca01/H13SC/" files "*.dta"
foreach file in `statafiles' {
  append using "`file'"
}
duplicates drop

gen HPCA_SC_COUNT_H13 = ch_total
gen HPCA_SC_bank = ch_using_banking/ch_total
gen HPCA_SC_assets_rt = ch_radio/ch_total
gen HPCA_SC_assets_tv = ch_tv/ch_total
gen HPCA_SC_assets_tel_l_m_b = ch_phone/ch_total
gen HPCA_SC_assets_bic = ch_bike/ch_total
gen HPCA_SC_assets_smm = ch_motorcycle/ch_total
gen HPCA_SC_assets_cjv = ch_car/ch_total
gen HPCA_SC_assets_none = ch_none/ch_total

save "$hpca01/hpca01_stata/H13SC_all.dta", replace
}
}

** Merge stata files and save raw variables
{
cd "$hpca01/hpca01_stata"
local files: dir "$hpca01/hpca01_stata" files "*.dta"
use "H1_all.dta", clear
cap drop HPCA*
foreach file in `files' {
  di "`file'"
	merge 1:1 st_code dt_code tahsil_code name rural_urban using "`file'", nogen
	cap drop HPCA*
}
compress
save "$hpca01/hpca01_all_raw.dta", replace
}


** Merge stata files and save HPCA variables only, forming 2001 HPCA datset
{
cd "$hpca01/hpca01_stata"
local files: dir "$hpca01/hpca01_stata" files "*.dta"
use "H1_all.dta", clear
keep st_code dt_code tahsil_code name rural_urban
foreach file in `files' {
  di "`file'"
	cap merge 1:1 st_code dt_code tahsil_code name rural_urban using "`file'", nogen keepusing(HPCA*) update
}

keep if rural_urban=="Rural"
gen level = word(name,1)
replace level = "Block" if inlist(level,"C.D.","Circle","Communue","Development","Mandal","Police","R.D.")
replace level = "Block" if inlist(level,"Sub-Division","Taluk","Tehsil","Tehsil/Sub-tehsil")

drop if level=="State"
destring st_code dt_code tahsil_code, replace
rename tahsil_code bk_code
replace name = subinstr(name,word(name,1),"",1)
replace name = subinstr(name,"-","",1)
replace name = subinstr(name,"*","",1)
replace name = subinstr(name,word(name,-1),"",1)
replace name = trim(itrim(upper(name)))

drop HPCA_A_ST* HPCA_A_SC*  HPCA_ST_* HPCA_SC_* // drop SC and ST variables, since we're not using them
drop HPCA_COUNT_H2    - HPCA_t_hh_rc_d     // keep H2 appendix (more observations)
drop HPCA_A_COUNT_H3A - HPCA_A_mat_r_aom   // drop H3A appendix (only at district level)
drop HPCA_A_COUNT_H3B - HPCA_A_mat_w_aom   // drop H3B appendix (only at district level)
drop HPCA_A_COUNT_H3C - HPCA_A_mat_f_aom   // drop H3C appendix (only at district level)
drop HPCA_COUNT_H4 - HPCA_hh_unclassif     // drop H4 appendix (more observations)
drop HPCA_COUNT_H6 - HPCA_not_owned_rented // drop H6 appendix (more observations)
drop HPCA_A_COUNT_H7 - HPCA_A_coup_5			 // drop H7A appendix (missing the zero couples category)

foreach v of varlist HPCA_A_* {
  local newname = "`v'"
	local newname = subinstr("`newname'","_A_","_",1)
	rename `v' `newname'
}
foreach v of varlist HPCA_* {
  local newname = "`v'"
	local newname = subinstr("`newname'","HPCA_","HPCA01_",1)
	rename `v' `newname'
}

  // for district-only variables, assign district averages to blocks
foreach v of varlist HPCA01_COUNT_H2-HPCA01_t_hh_rc_d HPCA01_COUNT_H5-HPCA01_coup_5 {
  egen temp = mean(`v'), by(st_code dt_code)
	replace `v' = temp if bk_code!=0
	drop temp
}

drop if HPCA01_COUNT_H10==0 & HPCA01_COUNT_H11==0 & HPCA01_COUNT_H13==0
drop if level=="District"
drop *COUNT* rural_urban level

order st_code dt_code bk_code name
rename name bk_name

*Labels
{
la var st_code "2001 state code"
la var dt_code "2001 district code"
la var bk_code "2001 block code"
la var bk_name "2001 block name"
la var HPCA01_t_hh_g "% HH that are 'good'"
la var HPCA01_t_hh_l "% HH that are 'livibale'"
la var HPCA01_t_hh_d "% HH that are 'delapidated'"
la var HPCA01_t_hh_r "% HH that are 'Residences'"
la var HPCA01_t_hh_r_g "% residence households that are 'good'"
la var HPCA01_t_hh_r_l "% residence households that are 'livibale'"
la var HPCA01_t_hh_r_d "% residence households that are 'delapidated'"
la var HPCA01_t_hh_r_o "% HH that are 'Residence-cum-other'"
la var HPCA01_t_hh_rc_g "% residence-cum-other households that are 'good'"
la var HPCA01_t_hh_rc_l "% residence-cum-other households that are 'livibale'"
la var HPCA01_t_hh_rc_d "% residence-cum-other households that are 'delapidated'"
la var HPCA01_mat_r_gtbw "% HH with roof made of grass/thatch/bamboo/wood/mud"
la var HPCA01_mat_r_pp "% HH with roof made of plastic/polythene"
la var HPCA01_mat_r_hmt_mmt "% HH with roof made of hand-made or machine-made tile"
la var HPCA01_mat_r_bb "% HH with roof made of burnt brick"
la var HPCA01_mat_r_ss "% HH with roof made of stone/slate"
la var HPCA01_mat_r_gmas "% HH with roof made of GI/metal/asbestos sheets"
la var HPCA01_mat_r_c "% HH with roof made of concrete"
la var HPCA01_mat_r_aom "% HH with roof made of any other material"
la var HPCA01_mat_w_gtb "% HH with walls made of grass/thatch/bamboo"
la var HPCA01_mat_w_pp "% HH with walls made of plastic/polythene"
la var HPCA01_mat_w_mub "% HH with walls made of mud/unburnt brick"
la var HPCA01_mat_w_w "% HH with walls made of wood"
la var HPCA01_mat_w_snpwm_spwm "% HH with walls made of stone (packed or not packed with mortar)"
la var HPCA01_mat_w_gimas "% HH with walls made of GI/metal/asbestos sheets"
la var HPCA01_mat_w_bb "% HH with walls made of burnt brick"
la var HPCA01_mat_w_c "% HH with walls made of concrete"
la var HPCA01_mat_w_aom "% HH with walls made of any other material"
la var HPCA01_mat_f_m "% HH with floor made of mud"
la var HPCA01_mat_f_wb "% HH with floor made of wood/bamboo"
la var HPCA01_mat_f_bb "% HH with floor made of burnt brick"
la var HPCA01_mat_f_st "% HH with floor made of stone"
la var HPCA01_mat_f_c "% HH with floor made of cement"
la var HPCA01_mat_f_mf "% HH with floor made of mosaic floor tiles"
la var HPCA01_mat_f_aom "% HH with floor made of any other material"
la var HPCA01_room_0 "% HH with no exclusive dwelling room"
la var HPCA01_room_1 "% HH with 1 room"
la var HPCA01_room_2 "% HH with 2 rooms"
la var HPCA01_room_3 "% HH with 3 rooms"
la var HPCA01_room_4 "% HH with 4 rooms"
la var HPCA01_room_5 "% HH with 5 rooms"
la var HPCA01_room_6plus "% HH with 6+ rooms"
la var HPCA01_h_size_1 "% HH with 1 person"
la var HPCA01_h_size_2 "% HH with 2 people"
la var HPCA01_h_size_3 "% HH with 3 people"
la var HPCA01_h_size_4 "% HH with 4 people"
la var HPCA01_h_size_5 "% HH with 5 people"
la var HPCA01_h_size_6_8 "% HH with 6-8 people"
la var HPCA01_h_size_9plus "% HH with 9+ people"
la var HPCA01_owned "% HH that are owned"
la var HPCA01_rented "% HH that are rented"
la var HPCA01_not_owned_rented "% HH with other ownership status"
la var HPCA01_coup_0 "% HH with 0 married couples"
la var HPCA01_coup_1 "% HH with 1 married couple"
la var HPCA01_coup_2 "% HH with 2 married couples"
la var HPCA01_coup_3 "% HH with 3 married couples"
la var HPCA01_coup_4 "% HH with 4 married couples"
la var HPCA01_coup_5 "% HH with 5+ married couples"
la var HPCA01_dw_tfts_tfus "% HH with tap water from treated or untreated source"
la var HPCA01_dw_cw_uw "% HH with water from covered or uncovered well"
la var HPCA01_dw_hp "% HH with water from handpump"
la var HPCA01_dw_tb "% HH with water from tubewell"
la var HPCA01_dw_sp "% HH with water from spring"
la var HPCA01_dw_rc "% HH with water from river/canal"
la var HPCA01_dw_tpl "% HH with water from tank/pond/lake"
la var HPCA01_dw_os "% HH with water from other sources"
la var HPCA01_w_within "% HH with water within premises"
la var HPCA01_w_near "% HH with water near premises"
la var HPCA01_w_away "% HH with water far from premises"
la var HPCA01_msl_elec "% HH with electricity as main lighting source"
la var HPCA01_msl_ker "% HH with kerosene as main lighting source"
la var HPCA01_msl_sol "% HH with solar energy as main lighting source"
la var HPCA01_msl_oo "% HH with other oil as main lighting source"
la var HPCA01_msl_ao "% HH with any other main lighting source"
la var HPCA01_msl_nl "% HH with no lighting"
la var HPCA01_lat_premise "% HH with latrine available with premises"
la var HPCA01_lat_pit_svi_sop "% HH with slab/ventilated or open pit latrine"
la var HPCA01_no_lat "% HH without latrine on premises"
la var HPCA01_bath_bath_ewr "% HH with bathing room available (with or without roof)"
la var HPCA01_bath_no "% HH with no bathing room"
la var HPCA01_ww_cd "% HH with closed drainage for waste water"
la var HPCA01_ww_od "% HH with open drainage for waste water"
la var HPCA01_ww_nd "% HH with no drainage for waste water"
la var HPCA01_cook_fw "% HH cooking with firewood"
la var HPCA01_cook_cr "% HH cooking with crop residue"
la var HPCA01_cook_cc "% HH cooking with cowdung cake"
la var HPCA01_cook_clc "% HH cooking with coal/lignite/charcoal"
la var HPCA01_cook_ker "% HH cooking with kerosene"
la var HPCA01_cook_lpg_png "% HH cooking with LPG/PNG"
la var HPCA01_cook_elec "% HH cooking with electricity"
la var HPCA01_cook_biog "% HH cooking with biogas"
la var HPCA01_cook_ao "% HH cooking with any other fuel source"
la var HPCA01_cook_no "% HH with no cooking"
la var HPCA01_cook_ih "% HH with cooking in home"
la var HPCA01_cook_ih_kitchen "% HH with kitchen in home"
la var HPCA01_cook_ih_no_kitchen "% HH with in-home cooking but no kitchen"
la var HPCA01_cook_oh "% HH with cooking outside of home"
la var HPCA01_cook_no2 "% HH with no cooking"
la var HPCA01_bank "% HH availing banking services"
la var HPCA01_assets_rt "% HH with radio/transister"
la var HPCA01_assets_tv "% HH with television"
la var HPCA01_assets_tel_l_m_b "% HH with phone (landline and/or mobile)"
la var HPCA01_assets_bic "% HH with bicycle"
la var HPCA01_assets_smm "% HH with scooter/motorcycle/moped"
la var HPCA01_assets_cjv "% HH with car/jeep/van"
la var HPCA01_assets_none "% HH with none of the specified assets"
la var HPCA01_hh_perm "% HH that are permanent"
la var HPCA01_hh_semi_perm "% HH that are semi-permanent"
la var HPCA01_hh_temp_total "% HH that are temporary"
la var HPCA01_hh_temp_serv "% HH that are temporary and serviceable"
la var HPCA01_hh_temp_nonserv "% HH that are temporary and nonserviceable"
la var HPCA01_hh_unclassif "% HH that have unclassifiable structures"
}

foreach v of varlist HPCA01* {
  local newname = subinstr("`v'","HPCA01","hpca01",.)
  rename `v' `newname'
}

drop if inlist(st_code,4,7,25,26,30,31,34,35)  // drop non-RGGVY states
duplicates drop
compress
save "$hpca01/hpca01_blockwise.dta", replace
}

********************************************************************************
********************************************************************************
